- Jan 26, 2015
- Office Version
AH!You could also consider limiting the array to 1500 rows per loop, something like this:
ary = ws.Range(ws.Cells(Ct * NumRws + 1, "A"), ws.Cells(NumRws * (Ct + 1), lastCol)).Value2
its perfect, so fast.
any idea how i would go about doing the inverse of this?
One that says for each sheet add the current region as part of the array?
Sub arrangeADD() Dim i As Long, j As Long Dim was As Worksheet Dim Ary As Variant Dim lastRow As Long Dim destRow As Long ThisWorkbook.Sheets.Add After:=ThisWorkbook.Sheets(1) Set ws = ActiveSheet For i = Worksheets("Program Start").Index + 1 To Worksheets("Description Helper").Index - 1 Ary = Sheets(i).Range("A1").CurrentRegion.Value2 Next i ws.Range("A1").Resize(UBound(Ary), 19).Value = Ary End Sub
so instead of limiting the array to 1500 from a sheet we add multiple sheets of a workbook to an array
so lets say we run your code and then we want to inverse it by adding the sheets between "Program Start" and "Description Helper" to an array
but my code here only copies the last sheet in the loop because i don't know how to add to an array i guess? if that makes sense