VBA to get data from separate workbooks by matching file and tab names

[athaung]

Hello,

I have a workbook - let's call it "Final" - with sheets labeled 52, 53, 54, ..., up to 81. I have separate workbooks in a folder on a network drive labeled meas52.xlsx, meas53.xlsx and so on. Each of those "meas##.xlsx" files contains three worksheets with information that I am trying to pull into its one corresponding worksheet in the "Final" workbook.

There seems to be some code out there that pulls info from a folder using the filepath etc., but I am struggling to modify it for my purposes. Below are the steps I would like to execute, and unfortunately do not have the syntax for in VBA.

1) Get the tab name in the "Final" workbook - e.g. "52"
2) Locate the filepath "C:\MyComputer\blahblah\Folder\meas"&"[tab number]"&".xlsx"

For each sheet in the "meas##.xlsx" workbook:
3) Copy contents from sheet
4) Paste as values in corresponding table in "Final workbook"

Basically, I want to write a loop that will run through and populate all the information in each of the tabs in the Final workbook! Please help!

Thanks,
Alison

 

[igold]

Hi Alison,

For numbers 3 & 4 above, could you give us an idea what the source data on the three sheets looks like and what the destination table looks like in the "Final Workbook"...

igold

 

[athaung]

Sure. There are three sheets that need to be copied, with tabs/worksheets entitled "table 1", "table 2", "table 3". Each table contains 5 columns - the first is a characteristic (e.g. average, minimum, 25th percentile etc), and each subsequent column is a year (2011-2014). Tables 2 and 3 are similar but have slightly different descriptives in the first column and Table 3 is just much longer.

I want to copy and paste the three tables into one sheet in a separate workbook ("Final" workbook, which contains summary descriptives of each of the meas##.xlsx files). Basically, the final workbook keeps the relative locations of all the data exactly the same, but just is a bit jazzed up and includes other data on a separate page of each worksheet.

I can send example files, but I'm not exactly sure how to do that on this feed here... :/