Summary with data from different worksheets Closed

Question

Hello, 
I have a file with 42 worksheets, all the same format (kind of "entry screens").  I need to create a summary table listing the data from all of these worksheets.

Column 1 of the summary table should e.g; list the values of cell A1 from each of the 42 worksheets, Column 2 e.g should list the B32 values from each of the 42 worksheets, etc.....

Anyone a suggestion how this can easily be done without having to manually enter the reference to the cells in the different worksheets into the summary table?
Thanks.

TrowaD · Answer

Hi Luc,

Try this code:
Sub test()
'Change the name of "Sheet1" as your MasterSheet (MS)
Const MS As String = "Sheet1"
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name = MS Then GoTo Nxt
ws.Range("A1").Copy Destination:=Sheets(MS).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
ws.Range("B32").Copy Destination:=Sheets(MS).Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
Nxt:
Next
End Sub
Best regards,
Trowa

Summary with data from different worksheets

1 response

Similar discussions

Subscribe To Our Newsletter!