For Each sheet in WkBook problem
,
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
|