0

I am trying to only run a set of macros if a sheet doesn't already exist. I have a macro that creates a sheet and combines data from two sheets into it, and another that formats the new sheet. Since it needs to run on workbook open, I can't have it recreating the sheet again and again. I have been trying the following, but it gives the error: "sub or Function not defined":

Private Sub Workbook_Open()
If SheetExist("MyNewSheet") Then
End Sub
Else
Combine
Format
End Sub
pnuts
  • 58,317
  • 11
  • 87
  • 139
Kode
  • 3,073
  • 18
  • 74
  • 140
  • Yea, the problem is "End Sub" should be "Exit Sub" You can also use the solution below. If SheetExist("MyNewSheet") Then Exit Sub – user1274820 Oct 22 '14 at 20:08

2 Answers2

2

You aren't doing anything if the sheet exists, so change your test.

Private Sub Workbook_Open()
    If Not SheetExist("MyNewSheet") Then
        Combine
        Format
    End If
End Sub

Function SheetExist(sheetname As String) As Boolean
    SheetExist = True ' replace this with code from link below
End Function

Use the answers here: Excel VBA If WorkSheet("wsName") Exists for examples of functions that determine whether the sheet exists.

Community
  • 1
  • 1
Jon Crowell
  • 21,695
  • 14
  • 89
  • 110
  • 1
    This is working: Private Sub Workbook_Open() If Not e("MyNewSheet") Then Combine Format End If End Sub Function e(n As String) As Boolean e = False For Each ws In Worksheets If n = ws.Name Then e = True Exit Function End If Next ws End Function – Kode Oct 22 '14 at 20:39
2

Yea, the problem is "End Sub" should be "Exit Sub" You can also use the solution above/below.

Your fixed code would be:

Private Sub Workbook_Open()

If SheetExists("MyNewSheet") Then
    Exit Sub
Else
    Combine
    Format
End If

End Sub

Also:

Public Function SheetExists(ByVal WorksheetName As String) As Boolean 

On Error Resume Next
WorksheetExists = (Sheets(WorksheetName).Name <> "")
On Error GoTo 0

End Function
user1274820
  • 7,786
  • 3
  • 37
  • 74
  • It is still giving me a compile error: Sub or Function not defined. Current code is: Private Sub Workbook_Open() If SheetExist("MyNewSheet") Then Exit Sub Else Combine Format End If End Sub Public Function SheetExists(ByVal WorksheetName As String) As Boolean On Error Resume Next WorksheetExists = (Sheets(WorksheetName).Name <> "") On Error GoTo 0 End Function – Kode Oct 22 '14 at 20:26
  • 1
    Should be If SheetExists my bad – user1274820 Oct 22 '14 at 20:40