Select all, but one, sheet?

C

Charlotte E

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,
 
R

ryguy7272

Try something like this:
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
If (sh.Name) <> "Periodebalancer" Then
'do something here
End if
Next


Dim sh As Worksheet
For Each sh In Worksheets
If (sh.Name) <> "Periodebalancer" Then
End If
Next sh

HTH,
Ryan---
 
G

Gary Keramidas

here's one way

Sub select_Sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Periodebalancer" Then
ws.Select False
End If
Next
End Sub
 
D

Dave Peterson

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
 
C

Charlotte E

Got it working - thanks :)


Dave said:
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
Click to expand...
 
N

norie

Charlotte

Is there a reason you want/need to select the sheets?

It generally isn't needed to do so, but without knowing exactly what
you are trying to do that's hard to be sure about.
 
C

Charlotte E

Is there a reason you want/need to select the sheets?

Well, perhaps not, but it is just the only way, I know how to accomplish my
goal:

I have a workbook where all the sheets end up in a report that needs to be
printed.
I cannot print the sheets one by one, since the length of the sheets can
vary each time the report is printed, and the only way I know to get the
page numbers, in the page footer, to increase right, is by selecting all the
sheets and then .PrintOut.

However, the data, which generates the report is on the sheet,
"Periodebalancer", which should not be in the report.
So, in order to get pagenumbers right, I need to select all the sheets,
except "Periodebalancer", before printing.

But, I'm fully aware, that in VBA it is rarely, if not never, necessary to
perform .Select, but in this special case, I simply don't know how else to
accomplish correct incrementation of the page numbers upon .PrintOut.

Hope this clearifies, and if anyone can tell me how to accomnplish my goal
without selecting the sheets, please, let me know :)
Problem is that I need to have the 'First page number' on each sheet set to
'Auto' since I don't know how many pages the previous sheet had.

But, now the entire macro, that generates and print the report, is working
as a charm, and fully automatically, and I'm quite pleased with it, so
thanks for your help, guys :)

BR,
CE
 
Top