PDA

View Full Version : Summarizing from multiple sheets in same workbook



ATan
06-26-2009, 01:22 AM
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

ATan
06-26-2009, 01:25 AM
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

ATan
06-26-2009, 09:03 PM
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
06-26-2009, 09:31 PM
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

ATan
06-26-2009, 09:38 PM
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
06-26-2009, 09:44 PM
: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

ATan
06-26-2009, 09:52 PM
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
06-26-2009, 10:03 PM
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?