VBA Copy from sequential workbooks into a single workbook with sequential columns

Hello

I have 52 workbooks that are going to be updated often. In each of them, I have 2 columns that need to be moved over into a single common workbook. The starting cell for the data is in the same location in all 52 workbooks. It will be 2 columns wide from the starting cell to the right and then down a dynamic amount of rows. It will then need to be placed in the appropriate columns in the common workbook (2 columns from each of the 52 files).

The files are named consecutively "XXXXX_Ch##_XXX_XXX" where ## represents the number 1 through 52 (note single digit numbers do not have 2 digits in the file name).

Is there some loop I can do where there is a variable in the name of the other files for the number so I don't have to copy the code 52 times and change the ## however many more times? Also, in this loop, I will need to add 2 to the position in the common workbook for columns as each of the files needs 2 columns pasted in.

Thank you anyone and everyone for any help!

Cheers

Wow! Thanks for the quick reply CK!

This looks like it will work fantastically... I have just a couple of easy for you questions ...

How do I get excel to open a file based on the variable path and variable file name?

Do I even need to open the file to copy contents from it?

Thanks again!

Wow! You are the best

I can't thank you enough for your speedy replies and time!