Code modification please

[Guest]

Hi all,
I use the following code to print all visible worksheets. Can it be modified
to NOT print a worksheet which is called "Main"?

thanks!

Sub Print_Visible_Worksheets()
'PRINT
'xlSheetVisible = -1
Dim sh As Worksheet
Dim arr() As String
Dim N As Integer
N = 0
For Each sh In ThisWorkbook.Worksheets
If sh.Visible = -1 Then
N = N + 1
ReDim Preserve arr(1 To N)
arr(N) = sh.Name
End If
Next
With ThisWorkbook
.Worksheets(arr).PrintOut
.Worksheets(1).Select
End With
End Sub

 

[Norman Jones]

Hi Tom,

Try something like:

'=============>>
Public Sub Print_Visible_Worksheets()
Dim sh As Worksheet
Dim arr() As String
Dim N As Long
Const sStr As String = "Main" '<<=== CHANGE

For Each sh In ThisWorkbook.Worksheets
With sh
If .Visible = xlSheetVisible Then
If StrComp(.Name, sStr, vbTextCompare) Then
N = N + 1
ReDim Preserve arr(1 To N)
arr(N) = .Name
End If
End If
End With
Next sh

With ThisWorkbook
.Worksheets(arr).PrintPreview 'PrintOut
.Worksheets(1).Select
End With
End Sub
'<<=============

 

[Guest]

Works like a charm!
Thanks Norman!

Hi Tom,

Try something like:

'=============>>
Public Sub Print_Visible_Worksheets()
Dim sh As Worksheet
Dim arr() As String
Dim N As Long
Const sStr As String = "Main" '<<=== CHANGE

For Each sh In ThisWorkbook.Worksheets
With sh
If .Visible = xlSheetVisible Then
If StrComp(.Name, sStr, vbTextCompare) Then
N = N + 1
ReDim Preserve arr(1 To N)
arr(N) = .Name
End If
End If
End With
Next sh

With ThisWorkbook
.Worksheets(arr).PrintPreview 'PrintOut
.Worksheets(1).Select
End With
End Sub
'<<=============

 

[Don Guillett]

Why not just

Sub printsheets()
For Each ws In Worksheets
If ws.Name <> "Main" And ws.Visible Then ws.PrintOut
Next ws
End Sub

Don Guillett
SalesAid Software
(e-mail address removed)