Reference to worksheets
 

I have a workbook with 45 worksheets. Each sheet has a row of totals which is
not in the same row number in every sheet. I want to create a 46'th sheet
with a summary of all the others, referring to these totals.

Is there an easy way, or must I do the one by one?

Reference to worksheets
 

Yes, there is an easier way. Put this macro in a standard module and run
it.
This macro will find the last row in each sheet and copy that row (5 columns
as written, starting with Column A)
and will paste the values (not the formulas) into the first empty row in a
sheet named "Summary" starting in Column B. Column A of each pasted row
will get the name of the source sheet. HTH Otto
Sub SummarizeData()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "Summary" Then GoTo SkipSht
With ws
.Range("A" & Rows.Count).End(xlUp).Resize(, 5).Copy
Sheets("Summary").Range("B" &
Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Sheets("Summary").Range("A" &
Rows.Count).End(xlUp).Offset(1).Value = ws.Name
End With
SkipSht:
Next ws
Application.ScreenUpdating = True
End Sub

"Thys" wrote in message
...
I have a workbook with 45 worksheets. Each sheet has a row of totals which
is
not in the same row number in every sheet. I want to create a 46'th sheet
with a summary of all the others, referring to these totals.

Is there an easy way, or must I do the one by one?

Reference to worksheets
 

Thanks Otto I'll try that!

"Otto Moehrbach" wrote:

Yes, there is an easier way. Put this macro in a standard module and run
it.
This macro will find the last row in each sheet and copy that row (5 columns
as written, starting with Column A)
and will paste the values (not the formulas) into the first empty row in a
sheet named "Summary" starting in Column B. Column A of each pasted row
will get the name of the source sheet. HTH Otto
Sub SummarizeData()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "Summary" Then GoTo SkipSht
With ws
.Range("A" & Rows.Count).End(xlUp).Resize(, 5).Copy
Sheets("Summary").Range("B" &
Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Sheets("Summary").Range("A" &
Rows.Count).End(xlUp).Offset(1).Value = ws.Name
End With
SkipSht:
Next ws
Application.ScreenUpdating = True
End Sub

"Thys" wrote in message
...
I have a workbook with 45 worksheets. Each sheet has a row of totals which
is
not in the same row number in every sheet. I want to create a 46'th sheet
with a summary of all the others, referring to these totals.

Is there an easy way, or must I do the one by one?

Reference to worksheets
 

This works great to move the last line. What if we need to move the entire
contents of the worksheet to a Summary sheet?

Otto Moehrbach wrote:
Yes, there is an easier way. Put this macro in a standard module and run
it.
This macro will find the last row in each sheet and copy that row (5 columns
as written, starting with Column A)
and will paste the values (not the formulas) into the first empty row in a
sheet named "Summary" starting in Column B. Column A of each pasted row
will get the name of the source sheet. HTH Otto
Sub SummarizeData()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "Summary" Then GoTo SkipSht
With ws
.Range("A" & Rows.Count).End(xlUp).Resize(, 5).Copy
Sheets("Summary").Range("B" &
Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Sheets("Summary").Range("A" &
Rows.Count).End(xlUp).Offset(1).Value = ws.Name
End With
SkipSht:
Next ws
Application.ScreenUpdating = True
End Sub

I have a workbook with 45 worksheets. Each sheet has a row of totals which
is
not in the same row number in every sheet. I want to create a 46'th sheet
with a summary of all the others, referring to these totals.

Is there an easy way, or must I do the one by one?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200603/1

Reference to worksheets
 

i want to use this macro
i am using excel2007
where can i find the standard module and how do i run can i have the steps pls

"Thys" wrote:

Thanks Otto I'll try that!

"Otto Moehrbach" wrote:

Yes, there is an easier way. Put this macro in a standard module and run
it.
This macro will find the last row in each sheet and copy that row (5 columns
as written, starting with Column A)
and will paste the values (not the formulas) into the first empty row in a
sheet named "Summary" starting in Column B. Column A of each pasted row
will get the name of the source sheet. HTH Otto
Sub SummarizeData()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "Summary" Then GoTo SkipSht
With ws
.Range("A" & Rows.Count).End(xlUp).Resize(, 5).Copy
Sheets("Summary").Range("B" &
Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Sheets("Summary").Range("A" &
Rows.Count).End(xlUp).Offset(1).Value = ws.Name
End With
SkipSht:
Next ws
Application.ScreenUpdating = True
End Sub

"Thys" wrote in message
...
I have a workbook with 45 worksheets. Each sheet has a row of totals which
is
not in the same row number in every sheet. I want to create a 46'th sheet
with a summary of all the others, referring to these totals.

Is there an easy way, or must I do the one by one?