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

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" <(E-Mail Removed)> wrote in message
news:CCE36462-92C2-47BD-BCC3-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
> news:CCE36462-92C2-47BD-BCC3-(E-Mail Removed)...
> > 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
>
>
>

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 Removed)
"Tom" <(E-Mail Removed)> wrote in message
news:CCE36462-92C2-47BD-BCC3-(E-Mail Removed)...
> 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