multiple worksheets to one sheet

[James Ryder]

I have a workbook that contains 130 worksheets. They are all of the
same structure and list details of products we sell.

I want to make one master list containing all the data from all 130
sheets. Is there any easy way to do this with a macro?

Any help greatly appreciated - Thanks.

 

[J.E. McGimpsey]

I have a workbook that contains 130 worksheets. They are all of the
same structure and list details of products we sell.

I want to make one master list containing all the data from all 130
sheets. Is there any easy way to do this with a macro?

Any help greatly appreciated - Thanks.

What exactly do you mean by "containing all the data" - do you
literally want to repeat each data point on the master list? Or do
you want to summarize/consolidate, e.g.:

D2: =SUM(Sheet1:Sheet130!D2)

to sum all of the cells in sheets between 1 and 130 (this is done by
position, not number, so if sheet2 is to the left of sheet1 it will
not be included).

If you mean to copy all the data from each sheet onto the master
sheet, a macro could certainly do that - how is your data laid out?

This will copy row 2 of each sheet to the master sheet:

Public Sub CopyToMaster()
Dim wkSht As Worksheet
Dim destSht As Worksheet
Dim destRow As Long

Set destSht = Sheets("Master")
destRow = 2&
For Each wkSht In Worksheets
If Not wkSht Is destSht Then _
wkSht.Rows(2).Copy destSht.Cells(destRow, 1)
destRow = destRow + 1&
Next wkSht
End Sub