software

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 quot;Summaryquot; 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 = quot;Summaryquot; Then GoTo SkipSht
With ws
.Range(quot;Aquot; amp; Rows.Count).End(xlUp).Resize(, 5).Copy
Sheets(quot;Summaryquot;).Range(quot;Bquot; amp;
Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Sheets(quot;Summaryquot;).Range(quot;Aquot; amp;
Rows.Count).End(xlUp).Offset(1).Value = ws.Name
End With
SkipSht:
Next ws
Application.ScreenUpdating = True
End Sub

quot;Thysquot; gt; wrote in message
...
gt;I have a workbook with 45 worksheets. Each sheet has a row of totals which
gt;is
gt; not in the same row number in every sheet. I want to create a 46'th sheet
gt; with a summary of all the others, referring to these totals.
gt;
gt; Is there an easy way, or must I do the one by one?
Thanks Otto I'll try that!

quot;Otto Moehrbachquot; wrote:

gt; Yes, there is an easier way. Put this macro in a standard module and run
gt; it.
gt; This macro will find the last row in each sheet and copy that row (5 columns
gt; as written, starting with Column A)
gt; and will paste the values (not the formulas) into the first empty row in a
gt; sheet named quot;Summaryquot; starting in Column B. Column A of each pasted row
gt; will get the name of the source sheet. HTH Otto
gt; Sub SummarizeData()
gt; Dim ws As Worksheet
gt; Application.ScreenUpdating = False
gt; For Each ws In ActiveWorkbook.Worksheets
gt; If ws.Name = quot;Summaryquot; Then GoTo SkipSht
gt; With ws
gt; .Range(quot;Aquot; amp; Rows.Count).End(xlUp).Resize(, 5).Copy
gt; Sheets(quot;Summaryquot;).Range(quot;Bquot; amp;
gt; Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
gt; Sheets(quot;Summaryquot;).Range(quot;Aquot; amp;
gt; Rows.Count).End(xlUp).Offset(1).Value = ws.Name
gt; End With
gt; SkipSht:
gt; Next ws
gt; Application.ScreenUpdating = True
gt; End Sub
gt;
gt; quot;Thysquot; gt; wrote in message
gt; ...
gt; gt;I have a workbook with 45 worksheets. Each sheet has a row of totals which
gt; gt;is
gt; gt; not in the same row number in every sheet. I want to create a 46'th sheet
gt; gt; with a summary of all the others, referring to these totals.
gt; gt;
gt; gt; Is there an easy way, or must I do the one by one?
gt;
gt;
gt;

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:
gt;Yes, there is an easier way. Put this macro in a standard module and run
gt;it.
gt;This macro will find the last row in each sheet and copy that row (5 columns
gt;as written, starting with Column A)
gt;and will paste the values (not the formulas) into the first empty row in a
gt;sheet named quot;Summaryquot; starting in Column B. Column A of each pasted row
gt;will get the name of the source sheet. HTH Otto
gt;Sub SummarizeData()
gt; Dim ws As Worksheet
gt; Application.ScreenUpdating = False
gt; For Each ws In ActiveWorkbook.Worksheets
gt; If ws.Name = quot;Summaryquot; Then GoTo SkipSht
gt; With ws
gt; .Range(quot;Aquot; amp; Rows.Count).End(xlUp).Resize(, 5).Copy
gt; Sheets(quot;Summaryquot;).Range(quot;Bquot; amp;
gt;Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
gt; Sheets(quot;Summaryquot;).Range(quot;Aquot; amp;
gt;Rows.Count).End(xlUp).Offset(1).Value = ws.Name
gt; End With
gt;SkipSht:
gt; Next ws
gt; Application.ScreenUpdating = True
gt;End Sub
gt;
gt;gt;I have a workbook with 45 worksheets. Each sheet has a row of totals which
gt;gt;is
gt;gt; not in the same row number in every sheet. I want to create a 46'th sheet
gt;gt; with a summary of all the others, referring to these totals.
gt;gt;
gt;gt; Is there an easy way, or must I do the one by one?

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

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

quot;Thysquot; wrote:

gt; Thanks Otto I'll try that!
gt;
gt; quot;Otto Moehrbachquot; wrote:
gt;
gt; gt; Yes, there is an easier way. Put this macro in a standard module and run
gt; gt; it.
gt; gt; This macro will find the last row in each sheet and copy that row (5 columns
gt; gt; as written, starting with Column A)
gt; gt; and will paste the values (not the formulas) into the first empty row in a
gt; gt; sheet named quot;Summaryquot; starting in Column B. Column A of each pasted row
gt; gt; will get the name of the source sheet. HTH Otto
gt; gt; Sub SummarizeData()
gt; gt; Dim ws As Worksheet
gt; gt; Application.ScreenUpdating = False
gt; gt; For Each ws In ActiveWorkbook.Worksheets
gt; gt; If ws.Name = quot;Summaryquot; Then GoTo SkipSht
gt; gt; With ws
gt; gt; .Range(quot;Aquot; amp; Rows.Count).End(xlUp).Resize(, 5).Copy
gt; gt; Sheets(quot;Summaryquot;).Range(quot;Bquot; amp;
gt; gt; Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
gt; gt; Sheets(quot;Summaryquot;).Range(quot;Aquot; amp;
gt; gt; Rows.Count).End(xlUp).Offset(1).Value = ws.Name
gt; gt; End With
gt; gt; SkipSht:
gt; gt; Next ws
gt; gt; Application.ScreenUpdating = True
gt; gt; End Sub
gt; gt;
gt; gt; quot;Thysquot; gt; wrote in message
gt; gt; ...
gt; gt; gt;I have a workbook with 45 worksheets. Each sheet has a row of totals which
gt; gt; gt;is
gt; gt; gt; not in the same row number in every sheet. I want to create a 46'th sheet
gt; gt; gt; with a summary of all the others, referring to these totals.
gt; gt; gt;
gt; gt; gt; Is there an easy way, or must I do the one by one?
gt; gt;
gt; gt;
gt; gt;