Looping through Worksheets to create Summary Page

  • Thread starter Thread starter Confused
  • Start date Start date
C

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?
 
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
 
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.
 
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
 
Thanks Don. That works perfectly.
What if I need to exclude one worksheet from the summary page?
 
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.
 
dlr =destinationlastrow
slr=.......
Could name it anything NOT reserved to MS
could have been
JOE or Bill or SEX or ....
 
Back
Top