Copy and Paste from worksheets within Workbook

[ATan]

My recent post here was a great learning experience and this is helping me to understand more about VBA. I have another question round about the same subject but with some difference.
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".
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.

[GTO]

Greetings ATan,

Could you post an example workbook with some rows of fake data?

Also, let's say sheet "WP2301" has 6 rows of data we want to copy over to the "Summary" sheet. Now a few days later, you add a couple of more sheets in the workbook, so the macro needs re-run. Can we put a "marker" in colum 12 (or some other column) on ea sheet as we copy data? This way we wouldn't recopy the same data.

Mark

[ATan]

GTO,

I am attaching a sample file named "WP2301" for reference. The rest of the sheets are of the same layout. I like your suggestion of putting a marker on column 12.

[GTO]

Greetings ATan,

Let's see if this works and seems sensible. I attached the wb, as I took a guess at how to setup the "Summary" sheet. Though I didn't test with add'l sheets, I see nothing to cause any "I ran the code and then called the fire department" type issues...

Hope this helps,

Mark

[vba]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[/vba]

[ATan]

Mark,

Thank you once again. I will be running the codes a few more times to see that it serve my purpose.