need help on a VBA code

New Contributor

Hi All, 

can someone please help with the following logic that I would need in VBA:


sheet1-4: row data with coulmn headings

sheet5: summary table with 4 rows of column headings representing the coloumn headings of sheet1-4 respectively (there are empty cells in some of the headings) 


I would need a macro that searches sheet5 column headings in rows 1 to 4, finds them in sheet 1-4 respectively and copy pastes the respective columns into sheet5 under the respective heading for sheet1 starting in row 2, for sheet2 starting in row 100, for sheet3 staritng row 200 and for sheet4 starting in row 300.


I very much appreciate your help with this.



5 Replies
Which Excel version are you using exactly?
Office 2016
You could do this by adding three queries to your file (Data, New Query button).
How can i merge tables there with headings that I need to link first (define which column heading in one table should be the equivalent of same with different name in another table)?
For each of the four sheets:
- You select the correct source data area
- Data, From Table
- In the right-hand pane, change the name to something relevant like qrySheet_1
- Now reorder the columns so they match the sheet you are currently targeting
- Delete any columns you don't need
- Rename any columns that need renaming
- Once you're happy, click the arrow below "Close & Load" and choose "Close & Load to..."
- In the next dialog select "Table" and select where you want the results to appear
- Do this for all four.
Now if your source data changes, just click the RefreshAll button.