Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Redimming an array dynamically assigned from range (how to redim first dimension of a 2-D array? /or/ reverse the original array order) | Excel Programming | |||
Test if Worksheet is not in an Array | Excel Programming | |||
Array from row in worksheet | Excel Programming | |||
Selecting Worksheet Array | Excel Programming | |||
Synax for two IF conditions in VBA | Excel Programming |