Code modification please

T

Tom

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
 
N

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
'<<=============
 
T

Tom

Works like a charm!
Thanks Norman!

Norman Jones said:
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
'<<=============
 
D

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top