ExcelBanter - Test for Worksheet

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Test for Worksheet (https://www.excelbanter.com/excel-programming/296893-test-worksheet.html)

Test for Worksheet

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

Test for Worksheet

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

--
Message posted from http://www.ExcelForum.com

Test for Worksheet

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

Test for Worksheet

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"?

-----Original Message-----
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
--
Regards,
Tom Ogilvy

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

.

Test for Worksheet

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

--
Regards,
Tom Ogilvy

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

-----Original Message-----
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
--
Regards,
Tom Ogilvy

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

.