Looping through Worksheets to create Summary Page

[Confused]

I have a workbook with about 20 worksheets. The worksheets contain the same
column names. I would like a macro that would create a summary page based on
the 20 worksheets.

Can anyone help me?

 

[Bernard Liengme]

Do you really need a macro?
Why not formulas such as:
=Sheet1!A1
=SUM('My First Sheet'!A1:A10)
=SUM('Alpha:Beta"!A1)
best wishes

 

[Confused]

I'm not quite sure what the formulas you gave me does. I need to consolidate
all the information from the 20 worksheets into one worksheet.

 

[Don Guillett]

Here is one I did earlier today

Option Private Module
Sub consolidatesheetsSAS() 'SalesAid Software
Application.ScreenUpdating = False

With Sheets("consolidated")
..Rows("2:" & Cells(2, 1).End(xlDown).Row).Delete

For Each sh In ActiveWorkbook.Sheets

If sh.Name <> "Consolidated" Then
dlr = .Cells(Rows.Count, 1).End(xlUp).Row + 1
slr = sh.Cells(Rows.Count, 1).End(xlUp).Row
If slr > 1 Then sh.Cells(3, 1).Resize(slr, 12).Copy .Cells(dlr, 1)
End If

Next sh

..Columns("A:L").HorizontalAlignment = xlCenter
End With

Application.ScreenUpdating = True
End Sub

 

[Confused]

Thanks Don. That works perfectly.
What if I need to exclude one worksheet from the summary page?

 

[Bernard Liengme]

You could use
=Sheet1!A1
=Sheet2!A1
=Sheet3!A1
=Sheet41!A1
=sum(these four)
to get a consolidation
best wishes
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

 

[Don Guillett]

If sh.Name <> "Consolidated" and _
sh.Name <> "othersheetname" Then

 

[Confused]

Thanks. Can you tell me what "DLR" and "SLR" mean in the code you wrote?
I'm trying to understand the code and learn from this.

 

[Don Guillett]

dlr =destinationlastrow
slr=.......
Could name it anything NOT reserved to MS
could have been
JOE or Bill or SEX or ....