The user's workflow is a perfect case study of why Excel's macro layer has become a bottleneck, not a solution. When your process is defined by opening and saving large files one after another, you are not automating, you are merely scripting the manual steps. The real speed gains come from skipping the application entirely.
What the user describes, extracting data from instruction files, creating folders, copying tabs, updating named ranges, and generating CSVs, is a classic batch data operation. VBA handles it by launching Excel, loading the file into memory, executing the edit, saving, and closing. Each open-and-save cycle costs seconds to minutes depending on file size. Multiply that by dozens or hundreds of files, and the total time becomes a function of how fast Excel can open and close, not how fast the data can be processed.
Python with pandas or openpyxl can read and write Excel files without ever starting Excel. The library reads the file's XML structure directly, manipulates the data in memory, and writes back out. For the user's task, updating named ranges, copying sheets, creating CSVs, this is dramatically faster because the overhead of launching the application is removed. A VBA macro that takes 10 seconds per file might take under one second in Python. Over a batch of 200 instruction files, that difference becomes 30 minutes versus 3 minutes.
PowerQuery is not the right tool for this job. It excels at transforming and loading data into a single table, but the user needs to create and modify multiple separate files, manage folder structures, and orchestrate conditional file copies. PowerQuery is a query engine, not a file-system scripting environment. Python gives the user full control over the operating system, file I/O, and Excel's internal structure.
The concrete path forward is to rewrite the VBA logic as a Python script using openpyxl for .xlsx files and pandas for .csv and tabular manipulation. Keep the instruction file as an Excel file or move it to a lightweight JSON or YAML format for even faster reads. The folders and file copies can be handled by Python's shutil module. The result is a system that runs as fast as the disk and CPU allow, not as fast as Excel decides to load. That is the difference between automation that feels like progress and automation that still feels like work.