Populate Master Worksheet from 5 Separate Worksheets

Hello, I have been on and off this forum and others trying to learn my way to performing a simple consolidation of the information contained within 5 identical worksheets into one Master Sheet (i.e. the Workbook has 6 worksheets total, 1 Master Sheet and 5 Worksheets). I am not brand new to Excel (2010 version) but compared to the knowledgeable individuals I've seen on here I am an infant. I am at a complete loss as to how to perform the task of making the data from each of the five spreadsheets populate into the Master Sheet. I tried the consolidation function, and was able to get the data consolidated into the master sheet, but I do not need the information to be totaled, averaged, Max, Min, Stddev, etc., so any non-numeric value does not appear in the Master Sheet. I just need for every row that is populated on each of the five spreadsheets to populate into the Master Sheet, and update as rows are added/changed in each of the five spreadsheets. I've attached an example of the worksheet and if anyone can help I would be so very grateful. Thanks!

Roger:

Thank you very much for your willingness to help me..! In your reply you referenced filling in the first few columns to make it work, but I am sorry I've looked and I do not see the "attachment". Nevertheless, given that you've provided the code as well, I guess now would be a better time than ever to learn macros. Again, thank you for your help, it is much appreciated!

Brian

Originally Posted by Roger Govier

Hi

The code in the attached workbook does what you want.
I had to fill in column A for a few rows on each sheet to test.

Please Login or Register  to view this content.

I am trying to run the Macro, but being that I haven't worked with them before I'm not sure that I'm doing it right at all. I've created a "Module" and within the Module I copied/pasted the exact code that you provided into the box, clicked run and then I get an error that says "Run-time Error 1004: Application-defined or Object-defined error". I also tried creating a Class Module instead of a Module and I get the identical error. My permissions are fully enabled (it says not recommended), and I've saved the workbook as a Macro Enabled Workbook too. I also populated the first three rows of each of the five worksheets that are to be copied into the Master Sheet. When the error comes up I've noticed that when I hit the Debug button the 5th Line of the provided code is highlighted. The 5th line says tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).ClearContents. Can someone please give me a hand if possible? Please see attached file.

Originally Posted by Roger Govier

Hi Brian
So sorry, I thought I had attached the file.
It all worked OK for me, but I can see that the part I added afterward to clear the Master sheet before copying, fails if the sheet is empty i.e. first time through.

I have added an On error resume next to get around this

Please Login or Register  to view this content.

Roger, the workbook works better than I could have imagined, thank you so very much! My last remaining question relates to the addition of rows to the Master Sheet beyond what was put there to setup the macro/workbook. In short, you placed the first 9 rows from each of the source sheets on the Master Sheet, and now that I've begun updating the source sheets, I'm assuming that once I get beyond Row 9 on one of the source sheets that when I click the "Copy Data" button on the Master Sheet the Rows beyond Row 9 will be copied over to the Master Sheet, correct? Again thank you so much!

Please find the attached worksheet (named “Revised.Workbook.11.28.1.C.xlsm”) that I’m having issues getting the Master Sheet to populate with the data from each of the 5 tabs. I have no way of knowing for sure but to me it appears the error is within the Macro, as the Macro (I’ve copied/pasted the text of the Macro below) contained within my original Workbook was created with Row 1 being my header row, and Rows 2-X containing the data that needed to populate into the Master Sheet. Now that the workbook has been revised Rows 1, 2 and 3 are for my header, and I think that is throwing off my Macro. Some of the time the data populates into the Master Sheet when I push the button, but when it does populate Rows 2 and 3 of each of the 5 tabs populates into the Master Sheet and that is not something that I have been able to fix. There are also occasions when I push the populate button and nothing happens, and the data doesn’t populate into the Master Sheet at all (not even Rows 2-3). I'm pretty new to Macro's and I've spent much time trying to reverse engineer the Macro to accommodate the altered configuration of the workbook, and since I don’t have much experience with Macro’s progress is slow at best. Anything you can do to help (I'm trying to get the information that is put on Tabs 2-6 to populate into the Master Sheet) would be greatly appreciated. Thanks!

Here is the working Macro “formula” from the original workbook, before I revised the Workbook:
Sub CopySheets()
Dim i As Long, tbl As Range
Application.ScreenUpdating = False
Set tbl = Sheets(1).Cells(1).CurrentRegion
On Error Resume Next
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).ClearContents
For i = 2 To 6
Set tbl = Sheets(i).Cells(1).CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Copy Sheets(1).Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1, 1)
Next
On Error GoTo 0
Application.ScreenUpdating = True
End Sub