Summarizing from multiple sheets in same workbook

[ATan]

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.

[ATan]

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]

If you simply want to perform the same as the macro you have given the change this line:[VBA]If wks.Name Like "WP####" Then [/VBA]for[VBA]If wks.Name Like "WP####" OR wks.Name ="ASM" Or wks.Name="Land" Or wks.name="Marine"Then [/VBA]or if not using your WP sheets then[VBA]If wks.Name ="ASM" Or wks.Name="Land" Or wks.name="Marine"Then[/VBA]

[ATan]

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?

[GTO]

Greetings Atan,

I did not test, but I believe you just want to try changing:
[vba] wks.Range("A" & lngRow + 1 & ":K" & lngLastRow).Copy _
[/vba]
TO:
[vba] wks.Range("A" & lngRow + 1 & ":V" & lngLastRow).Copy _
[/vba]

...as well as incorparating Simon's suggestions.

Hope that helps,

Mark

[ATan]

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

[GTO]

Oopsie! I believe you are correct.

While trying that, probably also needed is change:
[vba]
'// Then we'll refresh what row is 'marked' so we don't recopy the //
'// same info. //
wks.Range("L" & lngLastRow).Value = "Copied"
[/vba]
TO:
[vba]
'// Then we'll refresh what row is 'marked' so we don't recopy the //
'// same info. //
wks.Range("W" & lngLastRow).Value = "Copied"
[/vba]

Nice catch

Have a good weekend,

Mark

[ATan]

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?

[GTO]

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:
[vba]
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
[/vba]
...correct?