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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Confused" wrote in message
...
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?

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.

"Bernard Liengme" wrote:

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Confused" wrote in message
...
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?

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Confused" wrote in message
...
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?

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

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Confused" wrote in message
...
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?

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

"Confused" wrote in message
...
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.

"Bernard Liengme" wrote:

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Confused" wrote in message
...
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?

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Confused" wrote in message
...
Thanks Don. That works perfectly.
What if I need to exclude one worksheet from the summary page?

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Confused" wrote in message
...
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?

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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Confused" wrote in message
...
Thanks Don. That works perfectly.
What if I need to exclude one worksheet from the summary page?

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Confused" wrote in message
...
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?

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Confused" wrote in message
...
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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Confused" wrote in message
...
Thanks Don. That works perfectly.
What if I need to exclude one worksheet from the summary page?

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Confused" wrote in message
...
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?