Test for Worksheet

[Paul]

The code below is an excerpt from a larger macro. I am
working in worksheet "A" and want to activate a worksheet
called "B", if it exists. The way the code is written now,
I imagine that the macro will error out if it does not
find a worksheet named "B."

What code can I build in to ignore this section of code if
the worksheet does not exist?

TIA.

(I am working in worksheet "A" before I get to this
section of code.)

Workbooks("FIR_PATs.xls").Worksheets("B").Activate
total_row = Range("A65536").End(xlUp).Row
Range("A" & total_row - 2).Select
With ActiveCell
TtlD = .Offset(0, 3).Value
TtlE = .Offset(0, 4).Value
TtlF = .Offset(0, 5).Value
TtlG = .Offset(0, 6).Value
TtlH = .Offset(0, 7).Value
TtlI = .Offset(0, 8).Value
End With

 

[kkknie]

Try an error handler:

On Error Resume Next
Workbooks("FIR_PATs.xls").Worksheets("B").Activate
If Err.Number = 0 Then

On Error GoTo 0
total_row = Range("A65536").End(xlUp).Row
Range("A" & total_row - 2).Select
With ActiveCell
TtlD = .Offset(0, 3).Value
TtlE = .Offset(0, 4).Value
TtlF = .Offset(0, 5).Value
TtlG = .Offset(0, 6).Value
TtlH = .Offset(0, 7).Value
TtlI = .Offset(0, 8).Value
End With

End If
On Error GoTo 0

 

[Tom Ogilvy]

Dim sh as Worksheet
on error resume next
set sh = Workbooks("FIR_PATs.xls").Worksheets("B")
On Error goto 0
if not sh is nothing then
sh.parent.Activate
sh.Activate
total_row = Range("A65536").End(xlUp).Row
Range("A" & total_row - 2).Select
With ActiveCell
TtlD = .Offset(0, 3).Value
TtlE = .Offset(0, 4).Value
TtlF = .Offset(0, 5).Value
TtlG = .Offset(0, 6).Value
TtlH = .Offset(0, 7).Value
TtlI = .Offset(0, 8).Value
End With
End if

 

[Bob Phillips]

Here's my function that does it. Your code would use liek so

If IsWsSheet("B",Workbooks("FIR_PATs.xls")) Then
Workbooks("FIR_PATs.xls").Worksheets("B").Activate
total_row = Range("A65536").End(xlUp).Row
.Range("A" & total_row - 2).Select
With ActiveCell
TtlD = .Offset(0, 3).Value
TtlE = .Offset(0, 4).Value
TtlF = .Offset(0, 5).Value
TtlG = .Offset(0, 6).Value
TtlH = .Offset(0, 7).Value
TtlI = .Offset(0, 8).Value
End With
End If
'-----------------------------------------------------------------
Function IsWsSheet(Sh As String, Optional WB As Workbook) As Boolean
'-----------------------------------------------------------------
Dim oWs As Worksheet
If WB Is Nothing Then Set WB = ActiveWorkbook
On Error Resume Next
Set oWs = WB.Worksheets(Sh)
On Error GoTo 0
IsWsSheet = Not oWs Is Nothing
End Function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Paul]

This worked well. However, I attempted to use the routine
twice in my module, looking for worksheet "B" in one
section and then looking for worksheet "C" in another
section.

If it found "B", when the code got to looking for "C" it
did not skip the code section, even though "C" was not in
the workbook.

In the second iteration of the module I copied the code
from line two through the end. Is it obvious to you what I
am doing wrong? Do I need to "reset" the worksheet
variable or should I not be repeating the "on error
resume" or "on error goto 0"?

 

[Tom Ogilvy]

Dim sh as Worksheet
on error resume next
set sh = Workbooks("FIR_PATs.xls").Worksheets("B")
On Error goto 0
if not sh is nothing then
sh.parent.Activate
sh.Activate
total_row = Range("A65536").End(xlUp).Row
Range("A" & total_row - 2).Select
With ActiveCell
TtlD = .Offset(0, 3).Value
TtlE = .Offset(0, 4).Value
TtlF = .Offset(0, 5).Value
TtlG = .Offset(0, 6).Value
TtlH = .Offset(0, 7).Value
TtlI = .Offset(0, 8).Value
End With
End if

Set sh = Nothing '<=== Add this line

on error resume next
set sh = Workbooks("FIR_PATs.xls").Worksheets("C")
On Error goto 0
if not sh is nothing then
sh.parent.Activate
sh.Activate
total_row = Range("A65536").End(xlUp).Row
Range("A" & total_row - 2).Select
With ActiveCell
TtlD = .Offset(0, 3).Value
TtlE = .Offset(0, 4).Value
TtlF = .Offset(0, 5).Value
TtlG = .Offset(0, 6).Value
TtlH = .Offset(0, 7).Value
TtlI = .Offset(0, 8).Value
End With
End if