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


New Member
Jan 10, 2014

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!



Well-known Member
Jul 8, 2014
Office Version
365, 2010
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"...



New Member
Jan 10, 2014
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... :/

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...