Hello! Firstly thanks everyone for their help so far with my many questions. I really appreciate it and I am learning loads as a result.

i have a slightly more complex question i think...

i need to know if a worksheet exists in a given workbook. Over time, worksheets have been added, and removed, the result being that i have something like Sheet1, Sheet2, Sheet5, Sheet9 etc.

is worksheets(3) the same as worksheets("Sheet5") in the above example? If not, how can I loop through each to find out if the required worksheet exists?

Thanks

Patrick

Visit my site: http://www.drybonesuk.com

__________________
Visit my site: http://www.drybonesuk.com

Hi Patric,

Worksheets(3) uses index number and in your case Sheet5 is third sheet in worksheet collection.
If you move Sheet5 to another location in workbook, it´s not anymore Worksheets(3).

Here is one example how to loop all sheets

Sub LoopWorkSheets()
Dim ws As Worksheet

For Each ws In Worksheets
    If ws.Name = "Sheet5" Then
        'Do something
    End If
Next
End Sub

-vemaju

cycle through the existing sheets checking for the name you want - in this example I'm simply passing the name of the sheet I want and the function returns true if it exists and false if it doesn't..


Function checkSheet(mySheet As String) As Boolean
bFound = False
For Each Sheet In Worksheets
    If Sheet.Name = mySheet Then bFound = True
Next
checkSheet = bFound
End Function

Worksheets(3) is same as Worksheets("Sheet5") in the above example.

Regards
Ankur Verma

fantastic. thanks yet again for your replies. I have used the above example and it has worked fine. Thanks so much.

Patrick

Visit my site: http://www.drybonesuk.com