I am copying all worksheets that match a certain criteria to a new workbook.
The number of sheets will not be static every time the copy is run, thus
needing my array of sheets to be variable. How do you define the array of
sheets in a dynamic array as opposed to static?

Hi,
You can use something like this
'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets

' I enter this statement to be sure I pick up only tabs starting with es or
cs
If LCase(Left(sh.Name, 2)) = "es" Or LCase(Left(sh.Name, 2)) = "cs"
Then

'Find the last row with data on the DestSh
Last = lastRow(Sheets("BackLog_Summary"))

'Fill in the range that you want to copy
Set CopyRng = sh.Range("A4:AZ6")

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count
Sheets("BackLog_Summary").Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If

'This example copies values/formats, if you only want to copy the
'values or want to copy everything look at the example below
this macro
CopyRng.Copy
With Sheets("BackLog_Summary").Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

'Optional: This will copy the sheet name in the BA column
'Sheets("BackLog_Summary").Cells(Last + 1,
"BA").Resize(CopyRng.Rows.Count).Value = sh.Name

End If
Next

"jeremiah" wrote:

I am copying all worksheets that match a certain criteria to a new workbook.
The number of sheets will not be static every time the copy is run, thus
needing my array of sheets to be variable. How do you define the array of
sheets in a dynamic array as opposed to static?

Am rethinking my needs here because I need to copy each sheet to it's own
sheet in another workbook, would it make more sense to filter the sheets to
just the ones I need and then is it possible to save just the filtered sheets
to a new workbook?

"Eduardo" wrote:

Hi,
You can use something like this
'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets

' I enter this statement to be sure I pick up only tabs starting with es or
cs
If LCase(Left(sh.Name, 2)) = "es" Or LCase(Left(sh.Name, 2)) = "cs"
Then

'Find the last row with data on the DestSh
Last = lastRow(Sheets("BackLog_Summary"))

'Fill in the range that you want to copy
Set CopyRng = sh.Range("A4:AZ6")

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count
Sheets("BackLog_Summary").Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If

'This example copies values/formats, if you only want to copy the
'values or want to copy everything look at the example below
this macro
CopyRng.Copy
With Sheets("BackLog_Summary").Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

'Optional: This will copy the sheet name in the BA column
'Sheets("BackLog_Summary").Cells(Last + 1,
"BA").Resize(CopyRng.Rows.Count).Value = sh.Name

End If
Next

"jeremiah" wrote:

I am copying all worksheets that match a certain criteria to a new workbook.
The number of sheets will not be static every time the copy is run, thus
needing my array of sheets to be variable. How do you define the array of
sheets in a dynamic array as opposed to static?