vba test if sheet exsists

[bensonsearch]

Hi all,

i have the below code which runs a report. but its based on months, the sheets are named by the months and are added automatically if the month doesnt exsist. the problem i need to capture is if they select a month that isnt in the workbook it tells then so and exits sub. but i cant figure it out. below is the code in which I need this

Dim myWorksheet As Worksheet
    Dim myWorksheetName As String
    Dim count As Long
    Dim temp As String
    Dim count2 As Long
    
    If ComboBox1.value = "" Or ComboBox2.value = "" Then
    MsgBox ("Please select Month or Branch")
    Exit Sub
    End If
    
       temp = ComboBox2.value
    myWorksheetName = ComboBox1.value
    
        'need to test that myworksheetname exsists <--------
 

    count = Sheets(myWorksheetName).Range("A1").CurrentRegion.Rows.count
    
    Worksheets(myWorksheetName).Range("A1:P" & count).AutoFilter _
    field:=4, Criteria1:=temp
    
  
     
     Worksheets("Sheet1").Range("A1:P2").Copy _
    Destination:=Worksheets("Sheet3").Range("A1")
    
     Worksheets(myWorksheetName).Range("A3:M" & count).Copy _
    Destination:=Worksheets("Sheet3").Range("A3")
    
    count2 = Sheets("Sheet3").Range("A1").CurrentRegion.Rows.count
    count23 = count2 + 1
     Sheets("Sheet3").Range("K" & count23).value = "Total"
     Sheets("Sheet3").Range("L" & count23).value = "=SUM(L3:L" & count2 & ")"
    Sheets("Sheet3").Range("M" & count23).value = "=SUM(M3:M" & count2 & ")"
    
    With Sheets("Sheet3").PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
.PrintArea = ""
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0.92)
.BottomMargin = Application.InchesToPoints(0)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = True
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
   
   
    Sheets("Sheet3").Range("A1:M" & count23).PrintOut
    
     Worksheets(myWorksheetName).AutoFilterMode = False
    Sheets("Sheet3").Range("A1:P" & count23).value = ""
     ThisWorkbook.Save

thanx in advance

 

[Misca]

Try:

Function SheetExists(WSName As String) As Boolean
Dim WS As Worksheet
    
On Error Resume Next
Set WS = Worksheets(WSName)
    If Not WS Is Nothing Then SheetExists = True
End Function

 

[bensonsearch]

Hi 2 questions

how do i call the function and how does it know what wsname is?

 

[Misca]

The function works just like any function and you'll have to pass it the sheetname:

If SheetExists(ComboBox1.Value) = True Then
    'The sheet exists
Else
    'The sheet doesn't exist
End If