Sheets in workbook are A, B, C, D, E, F, G plus the two that are excluded in the code.

Total combined rows with data for all A to G sheets is about 52. (about 7 per sheet.)

If I run SheetsCopy1 I get about 5000 rows copied to Summary sheet, multiple repeats of correct data.

If I run SheetsCopy2 I see each sheet range to copy is properly selected and the Msgbox shows the correct sheet name.

The DeBug.Print lists the proper sheets A to G.

Code is in a standard module, and the downloaded example workbook looks to be German, saved as ...xlsm.

What little tid-bit am I overlooking here?

Thanks,
Howard

Sub SheetsCopy1()
Dim ws As Worksheet
Dim Rng As Range

For Each ws In ThisWorkbook.Sheets

If (ws.Name < "Summary") And (ws.Name < "Begin blad") Then

With ws

ws.Activate
Set Rng = Range("A2", Range("D2").End(xlDown))
Rng.Copy Sheets("Summary").Range("A" & Rows.Count).End(xlUp)(2)

End With

End If

Next
End Sub

Sub SheetsCopy2()
Dim ws As Worksheet
Dim Rng As Range
Dim lRow As Long

For Each ws In ThisWorkbook.Sheets

If (ws.Name < "Summary") And (ws.Name < "Begin blad") Then
'Debug.Print ws.Name
With ws
ws.Activate
Set Rng = Range("A2", Range("D2").End(xlDown))
Rng.Select
MsgBox ws.Name
End With

End If

Next
End Sub

Hi Howard,
Any time I have to consolidate data from multiple sheets to a 'summary'
sheet I use the following logic:

Dim wks As Worksheet, vData, lNextRow&

With Sheets("Summary")
'Get the next row position on the summary sheet
lNextRow = .Cells(1, .Rows.Count).End(xlUp).Row + 1

For Each wks In ThisWorkbook.Sheets
'Load the data range on each consolidation sheet into an array.
If (wks.Name < "Summary") And (wks.Name < "Begin blad") Then
vData = wks.Range("A2", wks.Range("D2").End(xlDown))

'Assign the array to the next row position
.Cells(lNextRow, 1).Resize(Ubound(vData), _
Ubound(vdata), 2) = vData

'Increment the next row position
lNextRow = lNextRow + Ubound(vData)
End If
Next 'wks
End With 'Sheets("Summary")

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

,
Any time I have to consolidate data from multiple sheets to a 'summary'
sheet I use the following logic:

Dim wks As Worksheet, vData, lNextRow&

With Sheets("Summary")
'Get the next row position on the summary sheet
lNextRow = .Cells(1, .Rows.Count).End(xlUp).Row + 1

For Each wks In ThisWorkbook.Sheets
'Load the data range on each consolidation sheet into an array.
If (wks.Name < "Summary") And (wks.Name < "Begin blad") Then
vData = wks.Range("A2", wks.Range("D2").End(xlDown))

'Assign the array to the next row position
.Cells(lNextRow, 1).Resize(Ubound(vData), _
Ubound(vdata), 2) = vData

'Increment the next row position
lNextRow = lNextRow + Ubound(vData)
End If
Next 'wks
End With 'Sheets("Summary")

--
Garry

Hi Garry,

Thanks for taking a look see.

This line errors with xlUP error value -4162

lNextRow = .Cells(1, .Rows.Count).End(xlUp).Row + 1

Tried with 15 +- rows of data already on Summary sheet and with only the headers on the sheet. Both give same error...?

Summary is a table for the A B C D columns, and the line failed, changed the table to a range, same result. Not sure that would make any difference anyway.

I also know that Excel has a personal vendetta out for me, given my luck with recent projects!

Howard

My bad! (aircode<g)

This..
lNextRow = .Cells(1, .Rows.Count).End(xlUp).Row + 1

...should be...

lNextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Thursday, November 26, 2015 at 5:03:00 PM UTC-8, GS wrote:
My bad! (aircode<g)

This..
lNextRow = .Cells(1, .Rows.Count).End(xlUp).Row + 1

..should be...

lNextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

--
Garry

Hi Garry,

Thanks, actually if I had studied the code harder, I should have seen that.

I did have to make a change on this line for the Resize columns.

..Cells(lNextRow, 1).Resize(UBound(vData), 4) = vData

Works great now.

Thanks for the array approach, I like the array shot at it, but it still whacks me most often.

Howard

On Thursday, November 26, 2015 at 5:03:00 PM UTC-8, GS wrote:
My bad! (aircode<g)

This..
lNextRow = .Cells(1, .Rows.Count).End(xlUp).Row + 1

..should be...

lNextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

--
Garry

Hi Garry,

Thanks, actually if I had studied the code harder, I should have seen
that.

I did have to make a change on this line for the Resize columns.

.Cells(lNextRow, 1).Resize(UBound(vData), 4) = vData

Works great now.

Thanks for the array approach, I like the array shot at it, but it
still whacks me most often.

Howard

Why did you hardcode the cols? The code uses the cols in vData, which
is 4 based on your range of A:D! You should always let the code use the
array sizes for the data...

Ubound(vData), 2) = 4 since it specifies the 2nd dim (#cols)

...because the range it holds spans cols A:D. You likely forgot that
loading a worksheet range into a variant results a 2D array.

I don't even use hard values for the source range because the data
resides in a dynamic range named "InputData" on every sheet. I didn't
want to take you there with this thread in case it went over your head,
but I do this...

vData = wks.Range("InputData")

...so the code is 100% reusable as well as better self-documenting.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion