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


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"bcmiller " wrote in message
...
Hi All,

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

So I have set up some code to do the calculations faster. Basically, I
have a control list of the worksheets that are report sheets that the
code should be executed on. I use a for loop to work through each
sheet and only want to perform the computations if a sheet matching the
entry in the list exists. The part where I am falling over is where I
have set a worksheet variable with a sheet name that doesn't exist. I
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 name
that does not exist, however the if statement says that it does.


Thanks in Advance.

BC


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