I have an Excel workbook with 4 sheets where I have 4 different list of inventory from 4 different work teams. All 4 sheets are identical except for their content which varies from one work team to another.
I would like to add a 5th sheet that includes all the contents from the inventory sheets (1 to 4). It would have the same format (same columns) but of course the number of rows will be the same as the sum of the rows from the first 4 sheets.
I found the following macro on the internet:
' MakeSummary Macro
' Macro created 6/12/2006 by Maniac
' Clear the existing values (if any)
Range("$A$2:$D$60").Value = ""
' J tracks the row number on the summary page
' I tracks the sheet number being processed
J = 2
For I = 2 To Sheets.Count
A$ = Sheets(I).Name
' Don't process a sheet if its name is "Conversion Table"
' or if the name is blank.
If (A$ = "Conversion Table") Then GoTo 10
If (Sheets(A$).Range("$C$1").Value = "") Then GoTo 10
' Process the current sheet
Range("A" + Format(J)).FormulaR1C1 = "='" + A$ + "'!R1C3"
Range("B" + Format(J)).FormulaR1C1 = "='" + A$ + "'!R2C3"
Range("C" + Format(J)).FormulaR1C1 = "='" + A$ + "'!R37C8"
Range("D" + Format(J)).FormulaR1C1 = "='" + A$ + "'!R38C8"
J = J + 1
10 Next I
I quite understand how this works but I have two problems.
My first problem is that inventory (items) are inside tables in the first 4 sheets. These tables are named so it would be easy to call them in the latest part of the macro. Just don't know how to do it.
The second problem is that the new (5th) table also have a table that needs to be filled but using this macro fills the first row of my table and the rest of the rows land outside (below) my table. My table is automatic so when you start typing in the next row, the table gets extended one row down... it doesn't happen with this macro.
Thanks in advance!