VBA to cycle through Workbooks, extract data and paste to a single workbook

[andysweeney]

HI all,

Struggling on this one despite seeing answers for some similar things.

I need some VBA to copy the data from Row 2 until the last populated row from every workbook in a designated folder into a single worksheet. The main issue that I have with previous answers is that the worksheets within any workbook are all named differently. (ie. not called "Sheet1")

Any help appreciated!

Andy

 

[Larry Haydn]

1. You have save all the required workbooks in a folder - that is great.
2. Now you can program the macro to:
a. Open this folder
b. For Each workbook in the folder, check the filename pattern (if required)
c. Open each workbook in turn (and identify that workbook positively, by assigning it a variable name)
d. For each open workbook, look for each tab (since they are named differently, if there is a particular name pattern, that is great)
e. If you can identify the tab name, proceed to copy.
f. If you do not know the tab name, then you may have to search for patterns in the worksheet column headings in each worksheet)
g. When you find the correct heading pattern, proceed to copy.
h. Finally, close the workbook.
i. Repeat till all workbooks have been copied.