Can't say for sure without looking at your file. If there's any reason
vData is not a 2D array then that line will definitely throw an error.
Try this on any sheet...


Sub test()
Dim v
v = Range("A1:D2")
Debug.Print "rows:=" & UBound(v)
Debug.Print "cols:=" & UBound(v, 2)
End Sub

..and it should return...

rows:=2
cols:=4

Garry

It returned 2 & 4 for every sheet, Summary & A to G, and the same for a new workbook made with same names and the data copied to each sheet.

I did notice while the .cells... line was yellow highlighted that the .End(xlDown) was showing -4162 when mouse over it.



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

Then another time it showed a date. Column D header is "Datum" and custom formatted to dd-mm-yyy.

I have no idea why it showed -4162 that one time.

Howard

Can't say for sure without looking at your file. If there's any
reason vData is not a 2D array then that line will definitely throw
an error. Try this on any sheet...


Sub test()
Dim v
v = Range("A1:D2")
Debug.Print "rows:=" & UBound(v)
Debug.Print "cols:=" & UBound(v, 2)
End Sub

..and it should return...

rows:=2
cols:=4

Garry

It returned 2 & 4 for every sheet, Summary & A to G, and the same
for a new workbook made with same names and the data copied to each
sheet.

I did notice while the .cells... line was yellow highlighted that the
.End(xlDown) was showing -4162 when mouse over it.



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

Then another time it showed a date. Column D header is "Datum" and
custom formatted to dd-mm-yyy.

I have no idea why it showed -4162 that one time.

Howard

Have a look in the ObjectBrowser for XlDirection, then click each item
listed to see its value. Here's what you should see...

xlDown = -4121
xlToLeft = -4159
xlToRight = -4161
xlUp - -4162

Sounds like you might need to close/reopen Excel to see if these
anomolies go away!

--
Garry

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

Hi Howard,

Am Thu, 26 Nov 2015 20:55:57 -0800 (PST) schrieb L. Howard:

you have a parenthis on the wron place

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



Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Howard,

Am Thu, 26 Nov 2015 20:55:57 -0800 (PST) schrieb L. Howard:

you have a parenthis on the wron place

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



Regards
Claus B.

..Ubound(vdata), 2) should be Ubound(vdata, 2))
Nice catch! Thank you, Claus<g

Sorry Howard, ..my bad!

--
Garry

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

Sorry Howard, ..my bad!

--
Garry


Hi Garry and Claus,

Thanks for clearing that up, I thought my Excel version was cursed and was having a ton of fun messing with me! (I still think it is out to get me though.)

Garry, the array code is an excellent reference for me. And the trouble shooting was a pretty good learning thing too.

Claus, neat and clever with the ASCII numbers codes. There will actually be A to Z sheets, each with names beginning with that letter.

Howard

Hi Howard,

Am Fri, 27 Nov 2015 00:14:34 -0800 (PST) schrieb L. Howard:

Claus, neat and clever with the ASCII numbers codes. There will actually be A to Z sheets, each with names beginning with that letter.

the loop with the ASCII numbers only works for sheets with only this
character. If you loop from A to Z and look for Left(Sheet.Name,1) you
will include "Summary" and "Begin Blad"


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Howard,

Am Fri, 27 Nov 2015 00:14:34 -0800 (PST) schrieb L. Howard:

Claus, neat and clever with the ASCII numbers codes. There will
actually be A to Z sheets, each with names beginning with that
letter.

the loop with the ASCII numbers only works for sheets with only this
character. If you loop from A to Z and look for Left(Sheet.Name,1)
you will include "Summary" and "Begin Blad"


Regards
Claus B.

I think you mean 'exclude'?

--
Garry

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

Hi Garry,

Am Fri, 27 Nov 2015 03:26:09 -0500 schrieb GS:

I think you mean 'exclude'?

Howard wrote that the sheets have names *beginning* with A to Z.
Then he cannot work with ASCII numbers because he had to look for
Left(Sheet.Name,1) and that would INCLUDE the sheets SUMMARY and BEGIN
BLADS.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Howard,

another suggestion:

Sub SheetsCopy()
Dim varData As Variant
Dim LRow As Long, i As Long

For i = 65 To 71
With Sheets(Chr(i))
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
varData = .Range("A2:D" & LRow)
Sheets("Summary").Cells(Rows.Count, 1).End(xlUp)(2) _
.Resize(UBound(varData), UBound(varData, 2)) = varData
End With
Next
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Howard,

another suggestion:

Sub SheetsCopy()
Dim varData As Variant
Dim LRow As Long, i As Long

For i = 65 To 71
With Sheets(Chr(i))
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
varData = .Range("A2:D" & LRow)
Sheets("Summary").Cells(Rows.Count, 1).End(xlUp)(2) _
.Resize(UBound(varData), UBound(varData, 2)) = varData
End With
Next
End Sub


Regards
Claus B.

Absolutely the way to go with production code; -eliminates the need to
increment the next position! It was my intent, though, to explain the
logic in a step-by-step fashion...

--
Garry

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

Hi Howard,

Am Fri, 27 Nov 2015 08:01:18 +0100 schrieb Claus Busch:

For i = 65 To 71

if you don't want to hardcode the ascii numbers and for more clarity and
logic you can change the code to:

Sub SheetsCopy()
Dim varData As Variant
Dim LRow As Long, i As Long

For i = Asc("A") To Asc("G")
With Sheets(Chr(i))
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
varData = .Range("A2:D" & LRow)
Sheets("Summary").Cells(Rows.Count, 1).End(xlUp)(2) _
.Resize(UBound(varData), UBound(varData, 2)) = varData
End With
Next
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Howard,

Am Fri, 27 Nov 2015 08:01:18 +0100 schrieb Claus Busch:

For i = 65 To 71

if you don't want to hardcode the ascii numbers and for more clarity
and logic you can change the code to:

Sub SheetsCopy()
Dim varData As Variant
Dim LRow As Long, i As Long

For i = Asc("A") To Asc("G")
With Sheets(Chr(i))
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
varData = .Range("A2:D" & LRow)
Sheets("Summary").Cells(Rows.Count, 1).End(xlUp)(2) _
.Resize(UBound(varData), UBound(varData, 2)) = varData
End With
Next
End Sub


Regards
Claus B.

I'm reading sheets A,B,C,D,E,F,G as being sheets 1,2,3,4,5,6,7!

So then using my reusable procedure...

Sub ConsolidateSheets()
' Consolidates data from all sheets into a Summary sheet
Dim vData, wks As Worksheet

For Each wks In ThisWorkbook.Sheets
If (wks.Name < "Summary") Then
'Load the data range into an array.
vData = wks.Range("InputData")
'Assign the array to the next row position
Sheets("Summary").Cells(.Rows.Count, 1).End(xlUp)(2) _
.Resize(UBound(vData), UBound(vData, 2)) = vData
End If
Next 'wks
End Sub

...needs only slight revision to exclude additional sheets. Typically,
though, I will store detail sheetnames in a constant...

In the declarations section of m_OpenClose (module):
Const gsDetailShts$ = "Wks1,Wks2"

Sub Consolidate_DetailShts()
' Consolidates data from detail sheets into a Summary sheet
Dim v
For each v in Split(gsDetailShts, ",")
vData = Sheets(v).Range("InputData")
Sheets("Summary").Cells(.Rows.Count, 1).End(xlUp)(2) _
.Resize(UBound(vData), UBound(vData, 2)) = vData
Next 'v
End Sub

...where the sheets to be consolidated are few in a multi-sheet wkb.

--
Garry

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