Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata

I now prefer to use a Sub to call this function and react accordingly like so:

This one won't delete if exists, the other will.

Public Sub WorksheetCreate(sName As String)
Dim sExists As Boolean
sExists = Evaluate("ISREF('" & sName & "'!A1)")
If sExists = False Then
With ActiveWorkbook
    .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = sName
End With
End If
End Sub
Function WorksheetExists(sName As String) As Boolean
    WorksheetExists = Evaluate("ISREF('" & sName & "'!A1)")
End Function
Public Sub WorksheetCreateDelIfExists(sName As String)
Dim sExists As Boolean
sExists = Evaluate("ISREF('" & sName & "'!A1)")
If sExists = False Then
With ActiveWorkbook
    .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = sName
End With
Else
 Application.DisplayAlerts = False
 Worksheets(sName).Delete
 Application.DisplayAlerts = True
With ActiveWorkbook
    .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = sName
End With
End If
End Sub
Function WorksheetExists(sName As String) As Boolean
    WorksheetExists = Evaluate("ISREF('" & sName & "'!A1)")
End Function


Test:


Public datews As Worksheet
Public Sub DateSheetCreateTest()
 Call DateSheetCreate("-SUBSA")
 datews.Activate
End Sub
Public Sub DateSheetCreate(sName As String)
 Dim DateVar As String
 DateVar = Format(Date, "DD-MM-YY")
 sName = DateVar & sName
 WorksheetCreate (sName)
 Dim wb As Workbook
 Set wb = ActiveWorkbook
 Set datews = wb.Worksheets(sName)
 'datews.Activate
End Sub

Sub DateSheet()
 Dim DateVar As String
 DateVar = Format(Date, "DD-MM-YY")
 Dim sName As String
 sName = DateVar & "-WS"
 WorksheetCreate (sName)
 Dim wb As Workbook, ws As Worksheet
 Set wb = ActiveWorkbook
 Set ws = wb.Worksheets(sName)
 ws.Activate
End Sub

Public Sub DateSheetCreateTest()
Call DateSheetCreate("-SUB")
End Sub
Public Sub DateSheetCreate(sName As String)
 Dim DateVar As String
 DateVar = Format(Date, "DD-MM-YY")
 sName = DateVar & sName
 WorksheetCreate (sName)
 Dim wb As Workbook, ws As Worksheet
 Set wb = ActiveWorkbook
 Set ws = wb.Worksheets(sName)
 ws.Activate
End Sub



Sub test()
 SheetExists = WorksheetExists("PivotTable")
 MsgBox SheetExists
End Sub
  • No labels