Synax help/ worksheet array
 

I am creating an array of worksheets that needs to exclude certain
worksheets. Ive 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


--
KT

Synax help/ worksheet array
 

I'd just create an array of strings (sheet names) and use that:

Option Explicit
Sub MoveSomeSheets()

Dim shArr() As String 'not worksheets
Dim shCount As Long
Dim sh As Object 'just a little better than Variant here
Dim TempWks As Worksheet

With ThisWorkbook
'make sure that there's at least one sheet that won't be moved
'so the workbook won't close after the move
Set TempWks = .Worksheets.Add
TempWks.Name = "DummyStr" & Format(Now, "yyyymmddhhmmss")

ReDim shArr(1 To .Sheets.Count)
shCount = 0
For Each sh In ThisWorkbook.Sheets
Select Case LCase(sh.Name)
Case Is = LCase("excludeTHISsheet"), _
LCase("excludeTHATsheet"), _
LCase(TempWks.Name)
'do nothing
Case Else
shCount = shCount + 1
shArr(shCount) = sh.Name
End Select
Next sh

If shCount = 0 Then
MsgBox "nothing to move"
Else
ReDim Preserve shArr(1 To shCount)
Sheets(shArr).Move
End If

'some clean up
If .Sheets.Count 1 Then
Application.DisplayAlerts = False
TempWks.Delete
Application.DisplayAlerts = True
Else
MsgBox "Couldn't delete " & TempWks.Name
End If
End With

End Sub

====
Actually, if you had any hidden sheets, you'd want to add some validity.
(Checking ".sheets.count 1" isn't enough.)

KT wrote:

I am creating an array of worksheets that needs to exclude certain
worksheets. Ive 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

--
KT

--

Dave Peterson

Synax help/ worksheet array
 

Perfect. Thanks Dave!
--
KT


"Dave Peterson" wrote:

I'd just create an array of strings (sheet names) and use that:

Option Explicit
Sub MoveSomeSheets()

Dim shArr() As String 'not worksheets
Dim shCount As Long
Dim sh As Object 'just a little better than Variant here
Dim TempWks As Worksheet

With ThisWorkbook
'make sure that there's at least one sheet that won't be moved
'so the workbook won't close after the move
Set TempWks = .Worksheets.Add
TempWks.Name = "DummyStr" & Format(Now, "yyyymmddhhmmss")

ReDim shArr(1 To .Sheets.Count)
shCount = 0
For Each sh In ThisWorkbook.Sheets
Select Case LCase(sh.Name)
Case Is = LCase("excludeTHISsheet"), _
LCase("excludeTHATsheet"), _
LCase(TempWks.Name)
'do nothing
Case Else
shCount = shCount + 1
shArr(shCount) = sh.Name
End Select
Next sh

If shCount = 0 Then
MsgBox "nothing to move"
Else
ReDim Preserve shArr(1 To shCount)
Sheets(shArr).Move
End If

'some clean up
If .Sheets.Count 1 Then
Application.DisplayAlerts = False
TempWks.Delete
Application.DisplayAlerts = True
Else
MsgBox "Couldn't delete " & TempWks.Name
End If
End With

End Sub

====
Actually, if you had any hidden sheets, you'd want to add some validity.
(Checking ".sheets.count 1" isn't enough.)

KT wrote:

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

--
KT

--

Dave Peterson