Select all, but one, sheet?
Got it working - thanks :-)
Dave Peterson wrote:
One way:
Option Explicit
Sub testme()
Dim myName As String
Dim ShArr() As String
Dim iCtr As Long
Dim aCtr As Long
Dim TestSh As Object
myName = "Periodebalancer"
With ActiveWorkbook
ReDim ShArr(1 To .Sheets.Count)
Set TestSh = Nothing
On Error Resume Next
Set TestSh = .Sheets(myName)
On Error GoTo 0
If TestSh Is Nothing Then
.Sheets.Select
Else
aCtr = 0
For iCtr = 1 To .Sheets.Count
If .Sheets(iCtr).Visible = xlSheetVisible Then
If .Sheets(iCtr).Name = TestSh.Name Then
'skip it
Else
aCtr = aCtr + 1
ShArr(aCtr) = .Sheets(iCtr).Name
End If
End If
Next iCtr
If aCtr = 0 Then
MsgBox "Nothing to select!"
Else
ReDim Preserve ShArr(1 To aCtr)
.Sheets(ShArr).Select
End If
End If
End With
End Sub
Charlotte E wrote:
I need my macro to select all sheets, no matter if they are charts or
worksheets, and no matter how they are named.
I know: Sheets.Select
But, I need my macro to select all, but one: If a sheet has the name
"Periodebalancer", then it should NOT be selected, but still the
rest of the sheets must still be selected.
Sometimes the sheet "Periodebalancer" is present, and sometimes not.
How to accomplish this???
TIA,
|