Check if a sheet exists

[Jon]

I have a workbook with a number of sheets (n) that changes.

Each sheet in the workbook is named; " Sheetn " where n is th
sheet number (e.g. Sheet1, Sheet2, Sheet3 etc.)

I need to know the VBA to check whether a sheet exists; something alon
the line of this

sheetloop = 1

do

domacroonsheet(sheetloop) <--- My other macro that does stuff

sheetloop = sheetloop + 1
loop until exist(sheets("Sheet" & sheetloop))=false


Can someone correct this for me please; I'm desperate to get thi
working!

Many thanks in advance! :confused

 

[Frank Kabel]

Hi
one way to check for an existing sheet:
dim wks as worksheet
on error resume next
set wks = worksheets("sheetname")
on error goto 0
if wks is nothing then
msgbox "sheet does not exist"
end if

 

[Jon]

Ok; I've coded this in. The problem is the loop drops out at 32000 is
without trapping the "Nothing" condition. Where have I mucked up?!

Dim numberoftitles As Integer
Dim looper As Integer
Dim wks As Worksheet

'Calculate Number Of Sheets
loopsheet = 0
Do
loopsheet = loopsheet + 1
On Error Resume Next
Set wks = Worksheets("Sheet" & loopsheet)
On Error GoTo 0
Loop Until wks Is Nothing
numberoftitles = loopsheet - 1


So close...! ;

 

[Frank Kabel]

Hi
not tested but try
Dim numberoftitles As Integer
Dim looper As Integer
Dim wks As Worksheet

'Calculate Number Of Sheets
loopsheet = 0
Do
set wks = nothing
loopsheet = loopsheet + 1
On Error Resume Next
Set wks = Worksheets("Sheet" & loopsheet)
On Error GoTo 0
Loop Until wks Is Nothing
numberoftitles = loopsheet - 1

 

[Jon]

Works a treat! Thanks Frank