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

**YeknomDude** · 10-03-2017, 12:39 PM

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

**CK76** · 10-03-2017, 12:51 PM

Simplest method is to leverage PowerQuery, which is free add-in for Excel 2010.

For VBA you can use variable to increment the number. Though if you want more detailed help, I'd suggest uploading sample files (both the source and output). Preferably 2 source files.

Ex:

Dim i as Long
Dim fpath as String: fpath = "C:\Test\" 'Change as needed
Dim fname as String
For i = 1 to 52
    fname = fpath & "XXXXX_Ch" & i & "XXX_XXX.xlsx"
   'use the fname variable in your copy paste or other means of transferring data
Next

**YeknomDude** · 10-03-2017, 01:28 PM

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!

**CK76** · 10-03-2017, 01:32 PM

Depends on method. For simple copy paste, yes you need to open file.

Something like below to open file.

Dim i as Long
Dim fpath as String: fpath = "C:\Test\" 'Change as needed
Dim fname as String
Dim sWb As Workbook
For i = 1 to 52
    fname = fpath & "XXXXX_Ch" & i & "XXX_XXX.xlsx"
    Set sWb = Workbooks.Open(fname)
   'then you can use sWb as object variable pointing to opened workbook
Next

**YeknomDude** · 10-03-2017, 01:40 PM

Wow! You are the best

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

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] copy sequential columns / paste to non-sequential columns

Copy/Drag from sequential column to non sequential ones while retaining sequention

Copy non-sequential columns with headers (from multiple sheets) to one consolidated sheet

[SOLVED] Create sequential letters (A, B, C) in sequential cells

How to copy sequential vales into non sequential rows

Plotting sequential data against sequential dates

Non-sequential VLOOKUP function -OR- sequential sort of web query

Bookmarks

Bookmarks

Posting Permissions