Finding a Sheet

[Guest]

I'm running an Access Database that creates worksheets in an Excel sheet.
The sheet is automatically named based on one of the criteria from the DB.
The problem I'm having is that I need to check if there is already a sheet in
the workbook with the same name as the one the DB is trying to use. I need
this to be part of the DB's coding, but can't seem to figure out a chunk of
code to to find the name of a worksheet. Any thoughts out there? I tried
the below, but that didn't work.

Sheets("Name_of_Sheet").Count

- Jeff

 

[JE McGimpsey]

One way:

Dim ws As Worksheet
On Error Resume Next
Set ws = ActiveWorkbook.Worksheets("Name_of_Sheet")
On Error GoTo 0
If Not ws Is Nothing Then
MsgBox "Duplicate Sheet"
Else
'Do Stuff
End If

 

[Guest]

This just prompts a "Run-time error '9': Subscript out of range" error.

- Jeff

 

[JE McGimpsey]

Replace

"Name_of_Sheet"

with your proposed worksheet name.

 

[Guest]

....um... yeah, I did that, not that green to all of this.

Don't worry about replying. I'll figure it out on my own.

- Jeff

 

[Dave Peterson]

Don't get rid of those error checking lines that JE added, too.

With "on error resume next" there, you shouldn't have gotten the error on that
section of code.

 

[JE McGimpsey]

Hard to tell how green one is from a ng post.

I suspect you didn't add the On Error Resume Next code, since you
shouldn't have gotten a Subscript Out Of Range if you had.

If you did, I can only conclude that the error is elsewhere in your code.