Merge/Consolidate Multiple Workbooks data into a Single Sheet

**COLIN_303** · 11-07-2012, 09:22 AM

Hi All

I want to create a Macro in the Master Workbook that opens Colins_TimeSheet, Copy the data from it and then close Colins_TimeSheet.

I want it to copy the Date (Column B), Columns D to N plus rows associated with the date (if there is data to be copied)

once copied, paste the data into the relevant columns in the Master Workbook, sheet named ‘Master'

Also Column E, titled Operator in the Master Spreadsheet, I want the name of the name of the sheet from colins_TimeSheet, entered along with any data copied across

I have found couple of Macros that sort of do what I want, but I am struggling to make them work, any help will be much appreciated

**arlu1201** · 11-07-2012, 09:26 AM

So both the master and the work codes tabs should be populated?

If you make any changes to the timesheets and want to run the macro again, should the master file be cleared (except the header) and then fresh data input?
Or should the data be copied to the next available row in the master file?

**COLIN_303** · 11-07-2012, 09:34 AM

The Work Codes Tabs are just there as a referance for the user of the Time Sheet and so can be ignored

The data is to be copied into the next available row in the master file.

thanks

**arlu1201** · 11-07-2012, 10:22 AM

I just looked at your Timesheet again.

How do you want the output to be shown from that data? It will be good if you upload a sample output file.

**COLIN_303** · 11-07-2012, 10:36 AM

attached is an example of how i would like the data to be copied from the TimeSheet to the Master Sheet

**arlu1201** · 11-07-2012, 02:50 PM

Try this code. Copy it to the Master file. Ensure that the master file is not saved in the same folder as the other files.

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose Tools | Macro | Macros
Select a macro in the list, and click the Run button