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