View Full Version : Summarizing from multiple sheets in same workbook
I have previously posted a question on the same subject but now I have a little chane to the workbook that I need to change the macro. I hope to get some help with this. Previously I have a workbooks with a "summary" sheet and other sheets named as "WP2301", "WP2407", "WP2403", "WP8402" and more will be added later but all with starting of "WP". But now I have sheets named as "ASM", "Land" and "Marine".
I now would like to copy from these sheets to the "summary" sheet. All these sheets have exact same layout with the header 6 rows deep and 11 columns wide. :help
Below is the codes from my previous post
Option Explicit
Sub Sheets_CopyData()
Dim wks As Worksheet
Dim lngLastRow As Long
Dim lngRow As Long
'// For ea worksheet in this workbook... //
For Each wks In ThisWorkbook.Worksheets
'// If the worksheet's name starts with "WP" and has four digits after... //
If wks.Name Like "WP####" Then
'// Since I figure the WP# is required, used Col A to run up and see where //
'// the last value is... //
lngLastRow = wks.Cells(Rows.Count, 1).End(xlUp).Row
'// ...now search from that row, to row 7, (searching in Col L), and we'll see //
'// if/where we find our marker ("Copied"). //
For lngRow = lngLastRow To 7 Step -1
If Not InStr(1, wks.Cells(lngRow, 12).Value, "Copied", vbTextCompare) = 0 Then
Exit For
End If
Next
'// Now if the last row (in Col A) that had a value in it, is farther down //
'// than the last row (in Col L) that has a 'marker', we know we have rows //
'// to copy. //
If lngLastRow > lngRow Then
wks.Range("A" & lngRow + 1 & ":K" & lngLastRow).Copy _
ThisWorkbook.Worksheets("Summary").Range("A" & _
ThisWorkbook.Worksheets("Summary") _
.Cells(Rows.Count, 1).End(xlUp).Row + 1 _
)
'// Then we'll refresh what row is 'marked' so we don't recopy the //
'// same info. //
wks.Range("L" & lngLastRow).Value = "Copied"
End If
End If
Next
End Sub
Simon Lloyd
06-26-2009, 02:15 AM
If you simply want to perform the same as the macro you have given the change this line:If wks.Name Like "WP####" Then forIf wks.Name Like "WP####" OR wks.Name ="ASM" Or wks.Name="Land" Or wks.name="Marine"Then or if not using your WP sheets thenIf wks.Name ="ASM" Or wks.Name="Land" Or wks.name="Marine"Then
Thanks for the help. I have another workbook basically the same but instead of the data range from Column A to K it is from Column A to V. What must I change in order for the macro to work in this other workbook?
Greetings Atan,
I did not test, but I believe you just want to try changing:
wks.Range("A" & lngRow + 1 & ":K" & lngLastRow).Copy _
TO:
wks.Range("A" & lngRow + 1 & ":V" & lngLastRow).Copy _
...as well as incorparating Simon's suggestions.
Hope that helps,
Mark
Thank you for your quick reply. In the code there is this line
If Not InStr(1, wks.Cells(lngRow, 12).Value, "Copied", vbTextCompare) = 0 Then do this have to be changed to
If Not InStr(1, wks.Cells(lngRow, 22).Value, "Copied", vbTextCompare) = 0 Then since now the number column has been expanded to 21
:blush Oopsie! I believe you are correct.
While trying that, probably also needed is change:
'// Then we'll refresh what row is 'marked' so we don't recopy the //
'// same info. //
wks.Range("L" & lngLastRow).Value = "Copied"
TO:
'// Then we'll refresh what row is 'marked' so we don't recopy the //
'// same info. //
wks.Range("W" & lngLastRow).Value = "Copied"
Nice catch :thumb
Have a good weekend,
Mark
GTO,
while running the codes I encountered a problem at this section of the codes
ThisWorkbook.Worksheets("INFRASTRUCTURE-SUMMARY").Range ("A" & _
ThisWorkbook.Worksheets("INFRASTRUCTURE-SUMMARY") _
.Cells(Rows.Count, 1).End(xlUp).Row + 1 _
) the error message said, "run-time error '438', Object doesn't support this property or method. What the reason for this error and how to overcome?
Hello,
At minimum, I'd need to see what's in front of that. A bit early to be this bleary eyed, but just to clarify - the above was intended to be the destination, similar to:
If lngLastRow > lngRow Then
wks.Range("A" & lngRow + 1 & ":K" & lngLastRow).Copy _
ThisWorkbook.Worksheets("Summary").Range("A" & _
ThisWorkbook.Worksheets("Summary") _
.Cells(Rows.Count, 1).End(xlUp).Row + 1 _
)
'// Then we'll refresh what row is 'marked' so we don't recopy the //
'// same info. //
wks.Range("L" & lngLastRow).Value = "Copied"
End If
...correct?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.