K
KT
I am creating an array of worksheets that needs to exclude certain
worksheets. I’ve adapted the following from code Per provided to add each
sheet to my array, but now I need to move my entire sheet array to a new
workbook. How do I refer to the entire array? Thanks!
Sub arrSh()
Dim shArr() As Worksheet
Dim shCount As Long
Dim sh As Variant
For Each sh In ThisWorkbook.Sheets
Select Case sh.Name
Case "excludeTHISsheet", "excludeTHATsheet"
'do nothing
Case Else
shCount = shCount + 1
ReDim Preserve shArr(1 To shCount)
Set shArr(shCount) = sh
End Select
Next
For sh = 1 To UBound(shArr)
Debug.Print shArr(sh).Name
Next
Worksheets(shArr).Move '<< THIS IS THE PROBLEM LINE. NEED SYNTAX TO MOVE ALL
SHEETS IN ARRAY TO NEW WB
End Sub
worksheets. I’ve adapted the following from code Per provided to add each
sheet to my array, but now I need to move my entire sheet array to a new
workbook. How do I refer to the entire array? Thanks!
Sub arrSh()
Dim shArr() As Worksheet
Dim shCount As Long
Dim sh As Variant
For Each sh In ThisWorkbook.Sheets
Select Case sh.Name
Case "excludeTHISsheet", "excludeTHATsheet"
'do nothing
Case Else
shCount = shCount + 1
ReDim Preserve shArr(1 To shCount)
Set shArr(shCount) = sh
End Select
Next
For sh = 1 To UBound(shArr)
Debug.Print shArr(sh).Name
Next
Worksheets(shArr).Move '<< THIS IS THE PROBLEM LINE. NEED SYNTAX TO MOVE ALL
SHEETS IN ARRAY TO NEW WB
End Sub