CODEWITHSUNDEEP
vbaexcelboolean
Mark O'Donnell
Mark O'Donnell

Reputation: 13

If Sheet Exists, run sub. If not, display message and exit sub

I am stuck despite looking up this topic on other threads. I would like help with the following:

If the macro can find a sheet named "Transfers," I would like it to activate that sheet and run the rest of the sub, which makes "Transfers" the last sheet.

If it can't find a sheet named "Transfers", I would like it so put up a message (Please make sure that you renamed your data sheet : 'Transfers'") and exit the sub. My code below is not working.

Sub Double_Transfer_Report()
Dim er As Boolean
er = False

On Error Resume Next
'Worksheets("Transfers").Activate
er = true

If er Then
MsgBox ("Please make sure that you renamed your data sheet : Transfers)
Exit Sub
End If

ActiveSheet.Move _
After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)

Upvotes: 1

Views: 2666

Answers (4)

teddy2
teddy2

Reputation: 390

Just another approach, not saying that it is any better than Cyboashu's (or else's answer).

Create a named Range with Xl4Macro such as SheetExists and put this in the Refersto: =SUBSTITUTE(GET.WORKBOOK(1),"[" &GET.WORKBOOK(16)&"]","")

Now on any sheet you can add this (array) formula and it will return back the sheet's actual index if its available.

{=MATCH("Sheet1091",TRANSPOSE(SheetExists),0)}

Just another way of doing things.:)

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96781

If you can spare two cells somewhere, you can avoid both looping and error handling, say we use B1 and B2. In B1 place the proposed worksheet name and fill B2 using:

Range("B2").Value = "=ISNUMBER(ROWS(INDIRECT(""'""&B1&""'!A1"")))"

Then check the Boolean result in B2:

enter image description here

Upvotes: 0

Excelosaurus
Excelosaurus

Reputation: 2849

Your idea is to attempt to activate the worksheet, and if this fails, take note than an error has occurred.

You can modify your code as shown below. The modification pertains to the way your er variable gets its value. After the Activate method fails, the Err object will contain the error number (and other details) until another error occurs or another On Error statement is encountered (among other things). Setting er, a boolean, to the logical test Err.Number <> 0 will detect that an error has occurred.

Sub Double_Transfer_Report()
    Dim er As Boolean

    er = False
    On Error Resume Next
    Worksheets("Transfers").Activate
    er = (Err.Number <> 0)
    On Error GoTo 0

    If er Then
        MsgBox "Please make sure that you renamed your data sheet : Transfers)"
    Else
        ActiveSheet.Move After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
    End If
End Sub

Upvotes: 2

cyboashu
cyboashu

Reputation: 10443

Don't do the gymnastic with error object. Just check if the sheet exists or not through looping. 

Sub Double_Transfer_Report()
    Dim found As Boolean

    found = SheetExists("Transfers")

    If Not found Then
        MsgBox "Please make sure that you renamed your data sheet : Transfers"
    Else
        ActiveSheet.Move After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
    End If
End Sub

Function SheetExists(strSheetName As String) As Boolean

    Dim wks As Worksheet

    For Each wks In ThisWorkbook.Worksheets

        If wks.Name = strSheetName Then
            SheetExists = True
            Exit Function
        End If
    Next

    SheetExists = False

End Function

Upvotes: 4

Related Questions