I have 200 workbooks that are 80% the same. One worksheet in each is an index
that has checkboxes for printing. I need to verify the presence of the sheet
to print before I issue the print command. Any sugguestions?

here is some code to let you know if a sheet exist in a workbook

Public Function SheetExists(SName As String, _
Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ThisWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function

You can pass it a workbook object to test or if you do not speify then it
looks at the workbook that is running the code...
--
HTH...

Jim Thomlinson


"dajns" wrote:

I have 200 workbooks that are 80% the same. One worksheet in each is an index
that has checkboxes for printing. I need to verify the presence of the sheet
to print before I issue the print command. Any sugguestions?

Use a function like

Function SheetExists(SheetName As String, Optional WB As
Workbook) As Boolean
On Error Resume Next
SheetExists = CBool(Len(IIf(WB Is Nothing, ThisWorkbook, WB)
_
.Worksheets(SheetName).Name))
End Function

Then call it with code like


If SheetExists("Sheet3",ThisWorkbook) = True Then
' sheet exists
Else
' sheet does not exist
End If



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"dajns" wrote in message
...
I have 200 workbooks that are 80% the same. One worksheet in
each is an index
that has checkboxes for printing. I need to verify the presence
of the sheet
to print before I issue the print command. Any sugguestions?

Hi dajns

You can use this function to test if the sheet exist in your code
If SheetExists("Sheet1") = False Then...................

Function SheetExists(SName As String, _
Optional ByVal wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If wb Is Nothing Then Set wb = ThisWorkbook
SheetExists = CBool(Len(wb.Sheets(SName).Name))
End Function




--
Regards Ron de Bruin
http://www.rondebruin.nl


"dajns" wrote in message ...
I have 200 workbooks that are 80% the same. One worksheet in each is an index
that has checkboxes for printing. I need to verify the presence of the sheet
to print before I issue the print command. Any sugguestions?

Ron, Thank you very much. I will begin coding asap!
dajns

"Ron de Bruin" wrote:

Hi dajns

You can use this function to test if the sheet exist in your code
If SheetExists("Sheet1") = False Then...................

Function SheetExists(SName As String, _
Optional ByVal wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If wb Is Nothing Then Set wb = ThisWorkbook
SheetExists = CBool(Len(wb.Sheets(SName).Name))
End Function




--
Regards Ron de Bruin
http://www.rondebruin.nl


"dajns" wrote in message ...
I have 200 workbooks that are 80% the same. One worksheet in each is an index
that has checkboxes for printing. I need to verify the presence of the sheet
to print before I issue the print command. Any sugguestions?

Chip,
Thank you very much. I appreciate the help.
dajns

"Chip Pearson" wrote:

Use a function like

Function SheetExists(SheetName As String, Optional WB As
Workbook) As Boolean
On Error Resume Next
SheetExists = CBool(Len(IIf(WB Is Nothing, ThisWorkbook, WB)
_
.Worksheets(SheetName).Name))
End Function

Then call it with code like


If SheetExists("Sheet3",ThisWorkbook) = True Then
' sheet exists
Else
' sheet does not exist
End If



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"dajns" wrote in message
...
I have 200 workbooks that are 80% the same. One worksheet in
each is an index
that has checkboxes for printing. I need to verify the presence
of the sheet
to print before I issue the print command. Any sugguestions?

Assuming you are looping through the checkboxes and printing the sheets one
at a time, you could just ignore the error

On Error Resume Next
worksheets(i).Printout
On Error goto 0

--
Regards,
Tom Ogilvy


"dajns" wrote:

I have 200 workbooks that are 80% the same. One worksheet in each is an index
that has checkboxes for printing. I need to verify the presence of the sheet
to print before I issue the print command. Any sugguestions?

Jim, Thank you very much.
dajns

"Jim Thomlinson" wrote:

here is some code to let you know if a sheet exist in a workbook

Public Function SheetExists(SName As String, _
Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ThisWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function

You can pass it a workbook object to test or if you do not speify then it
looks at the workbook that is running the code...
--
HTH...

Jim Thomlinson


"dajns" wrote:

I have 200 workbooks that are 80% the same. One worksheet in each is an index
that has checkboxes for printing. I need to verify the presence of the sheet
to print before I issue the print command. Any sugguestions?

Chip,
I am having a problem get something to print. This is my first time creating
a function, so I've made some errors. Could you look at this code and set me
on the right track?

Function SheetExists(SName As String) As Boolean
On Error Resume Next
End Function

Private Sub CommandButton3_Click()
'***** PRINT ALL BUTTON *****
'***** Print the Cover Tab *****
If SheetExists("Cover") = True Then
Sheets("Cover").Select
'ActiveSheet.PageSetup.PrintArea = "$A$1:$M$28"
'ActiveSheet.PageSetup.LeftMargin =
Application.InchesToPoints(0.25)
'ActiveSheet.PageSetup.RightMargin =
Application.InchesToPoints(0.25)
'ActiveSheet.PageSetup.CenterHorizontally = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
' Sheets("Index").Select
' Range("A1").Select
'***** Print the Index Tab *****
If SheetExists("Index") = True Then
Sheets("Index").Select
'ActiveSheet.PageSetup.PrintArea = "$D$1:$R$38"
'ActiveSheet.PageSetup.LeftMargin =
Application.InchesToPoints(0.25)
'ActiveSheet.PageSetup.RightMargin =
Application.InchesToPoints(0.25)
'ActiveSheet.PageSetup.CenterHorizontally = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
' Sheets("Index").Select
' Range("A1").Select
'***** Print the Balance Sheet Tab *****
If SheetExists("BS") = True Then
Sheets("BS").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
' Sheets("Index").Select
' Range("A1").Select
'***** Print the Cash Position Tab *****
If SheetExists("CashPosition") = True Then
Sheets("CashPosition").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If

What did I mess up? I really appreciate your help!!

"Chip Pearson" wrote:

Use a function like

Function SheetExists(SheetName As String, Optional WB As
Workbook) As Boolean
On Error Resume Next
SheetExists = CBool(Len(IIf(WB Is Nothing, ThisWorkbook, WB)
_
.Worksheets(SheetName).Name))
End Function

Then call it with code like


If SheetExists("Sheet3",ThisWorkbook) = True Then
' sheet exists
Else
' sheet does not exist
End If



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"dajns" wrote in message
...
I have 200 workbooks that are 80% the same. One worksheet in
each is an index
that has checkboxes for printing. I need to verify the presence
of the sheet
to print before I issue the print command. Any sugguestions?