Reference to worksheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
Thanks Otto I'll try that!

Otto Moehrbach said:
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
Click to expand...
 
This works great to move the last line. What if we need to move the entire
contents of the worksheet to a Summary sheet?
 
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
 
Back
Top