Code modification please
 

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

Code modification please
 

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


---
Regards,
Norman


"Tom" wrote in message
...
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

Code modification please
 

Works like a charm!
Thanks Norman!

"Norman Jones" wrote:

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


---
Regards,
Norman


"Tom" wrote in message
...
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

Code modification please
 

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

"Tom" wrote in message
...
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