Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
VLookup using 2 worksheets, whole column reference | Excel Worksheet Functions | |||
Merge Worksheets | Excel Discussion (Misc queries) | |||
Merge Worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |