Test for Worksheet Exists

B

bcmiller

Hi All,

This is probably a simple one that I have managed to overlook but her
goes. I have a workbook that contains some data sheets and repor
sheets. Each report sheet is identical and extracts similar data fro
the data sheet. I can set this up using formulas but it takes foreve
to calculate (over 30,000 formulas).

So I have set up some code to do the calculations faster. Basically,
have a control list of the worksheets that are report sheets that th
code should be executed on. I use a for loop to work through eac
sheet and only want to perform the computations if a sheet matching th
entry in the list exists. The part where I am falling over is where
have set a worksheet variable with a sheet name that doesn't exist.
can't find a test that works. Here is a code sample:

On Error Resume Next
For a = LBound(CostCentreList) To UBound(CostCentreList)
CostCentreList(a) = CCRange.Cells(a + 1, 1).Value
Next

.........

For i = 0 To CCListLength - 1
Set ExpSheet = wkb.Worksheets(CostCentreList(i))
If IsEmpty(ExpSheet) = False Then

............
The value of ExpSheet here is 'Nothing' as I have used a sheet nam
that does not exist, however the if statement says that it does.


Thanks in Advance.

B
 
R

Rob van Gelder

Here's a test for a worksheet

Sub test()
Dim wks As Worksheet, bln As Boolean

On Error Resume Next
Set wks = Worksheets("sheet1")
bln = (Err.Number = 0)
On Error GoTo 0

MsgBox bln

End Sub
 
B

bcmiller

Hi Rob,

In the context of my code, your suggestion does not work. The sam
thing is happening that the code believes there isn't a problem an
keeps executing, probably because the bln error test evaluates t
false. So it looks like I am still stuck.


Cheers,

B
 
B

bcmiller

Case Closed:

I just need to take your code to the next step and now it works.

Baically, use bln in an if statement to determine whether to procee
with the code execution or to generate an error report with all th
items that had no corresponding sheet and could not be processed.
Thanks for the assistance.

Final Code:

For i = 0 To CCListLength - 1
Set ExpSheet = wkb.Worksheets(CostCentreList(i))
bln = (Err.Number = 0)
On Error GoTo 0
If bln = True Then

Many appreciations,

B
 
Top