Copy multiple monthly worksheets to master sheet

[Malkier]

Hi,

There are lots of similar threads out there for this problem but they are just a little too different.

I have two excel files:

-Workbook1: Currently, varying numbers of rows of data on Monthly sheets,................. I want: to dynamically display data from all sheets in a 'master sheet' in the same workbook (ie the master always shows up-to-date data).
-Workbook2: Has formula that will search the master sheet in the above workbook and return all rows that match a set criteria.

The question I would like solved is: How can I append data from the sheets in Workbook1 into one sheet? All data will have the same column headings but each sheet may have anywhere from 0-150, or more, rows of data. Ideally I would like the master to be updated when new data is entered on any of the monthly sheets.

I would prefer a formula (that I can understand and perhaps use again) rather than VBA code but if someone can meet the above requirements with VBA then I might be swayed.

Thanks in advance...

[JBeaucaire]

I would use a Worksheet_Activate macro built into the Master sheet code module. Any time you bring that sheet up onscreen it would instantly:

1) Delete all the current data on the master sheet
2) Cycle through ALL other sheets and copy the data into the master to refresh everything
3) Optionally sort by a specific column to put all the data into a more organized consolidated table


To do this:

1) Right click the sheet master sheet tab and select VIEW CODE
2) Paste in this event macro:

Please Login or Register  to view this content.

3) Close the vbeditor and save the workbook in a macro-enabled format like *.xlsm

Now that sheet will update itself anytime you view it from another another sheet.

[Malkier]

Thanks Jerry - That is a great help!!

Is there a way to remove the recurring headers as I will be using this as a database? i.e. perhaps have the data and not the headers pasted in?

Also - I am going to be using a formula in another workbook to search this master sheet - is there a macro I can put in the other sheet to run the above code to make sure I get the most up to date information?

Thanks again!!

[JBeaucaire]

The headers (row1) are being copied in from the first sheet only, the macro comments point out where that is occurring, only the data from row2 down on ALL sheets is copied after that.

[Malkier]

The headers (row1) are being copied in from the first sheet only, the macro comments point out where that is occurring, only the data from row2 down on ALL sheets is copied after that.

Thanks Jerry - despite this intention it seems I am getting headers copied from every sheet...

This may belong as a separate question but...
I am now thinking about permanently moving these sheets into another workbook - I experimented with this, but it copied the data from all sheets - which is what I was looking for in the first place!

Can I specify a beginning and end sheet (e.g. Master sheet and a sheet called End - with no data on it?) and have the macro loop through the sheets in between?

Also posted the question here - http://stackoverflow.com/questions/2...ts-into-master

[JBeaucaire]

Yes, this would be a separate question.