Test for Worksheet Exists
 

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

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

Test for Worksheet Exists
 

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/

Test for Worksheet Exists
 

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

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

Test for Worksheet Exists
 

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

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

Test for Worksheet Exists
 

You've got it... nice work :)

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


"bcmiller " wrote in message
...
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 proceed
with the code execution or to generate an error report with all the
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,

BC


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