New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Determine if a Worksheet Exists

Free Excel Macros

Determine if a Worksheet Exists

Postby Sisyphus » Fri Feb 10, 2012 8:13 am

If your task is to extract data from one or several specific worksheets you will find this procedure useful:
Code: Select all
Private Function FindSheet(ByVal TargetSheet As String) As Long

    Dim i As Integer
   
    With ThisWorkbook
        For i = .Worksheets.Count To 1 Step -1
            If .Worksheets(i).Name = TargetSheet Then Exit For
        Next i
    End With
    FindSheet = i
End Function

The argument this function needs is the name of a worksheet. it will return the index number of that worksheet in the Sheets collection or, if it is not found, 0. It is for this latter effect that the search is conducted backward. As you can address a worksheet either by its name or its number the primary use of this function is to ascertain if the worksheet exists.

The following procedure is called Test(). In fact, it is more like a procedure that would, once the above function is integrated into a project, act as the calling procedure that processes the information provided. That is why it is declared as Public (so that it can be called from Tools / Macro / Macros), and that is also the reason why it is bigger in size than the function that feeds it.
Code: Select all
Public Sub Test_FindSheet()
   
    Dim Ws As Worksheet
    Dim Wn As String            ' Worksheet Name
    Dim i As Integer
    Dim Ttl As String
   
    Wn = ActiveSheet.Name       ' substitute with any String
    i = FindSheet(Wn)
    Ttl = "Sheet index"
    If i Then
        MsgBox "The sheet was found." & vbCr & _
               "Its index is No. " & i, vbInformation, Ttl
        ' you can insert code here that extracts
        ' or inserts data from or into the identified sheet
        ' for example:
        Set Ws = ThisWorkbook.Worksheets(i)
        Ws.Cells(1, 1).Value = 1122                    ' write to A1
        Debug.Print Ws.Cells(1, 1)                     ' read from A1
    Else
        MsgBox "A sheet by the name of" & vbCr & _
               Wn & " exists.", vbCritical, Ttl
        ' write code here that can handle the event
        ' that the worksheet isn't available
        ' f.i create the worksheet
    End If
End Sub

Nevertheless, the test procedure also demonstrates how a tertiary function like 'FindSheet' should be tested.

Both procedures should be pasted to a normal code module, such as Module1. If you decide to test this code in your own workbook, note that it will write to A1. To see the result of its reading you need to display the Immediate Window in the VB editor window (Ctl+G).
The variable Wn (Worksheet Name) has been set here to the name of the ActiveSheet. Therefore it is guaranteed to be found. For testing purposes you can set this variable to take any name at all, existing or not. When you integrate the assembly into your project Wn is likely to be determined by your preceding code. You would use this kind of code to extract data from or write data to selected sheets from many. If you wanted similar data from or send the same data to every sheet in a workbook you would probably use a construct that is less selective than this one.
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 21 guests