Check if sheetname exists before create

Vincent88

Active Member
Joined
Mar 5, 2021
Messages
382
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
I run my code to duplicate a worksheet. If the worksheet is already existed , then no duplication of sheet and message box (Sheet (sheetname) does exists) will show. But this message box will also pop up when new sheet is created but I do not want this. I only want this msg box to pop up when I run the marco again and found the same sheetname already there. Please help.

VBA Code:
Function DoesSheetExists(sh As String) As Boolean
    Dim ws As Worksheet

    On Error Resume Next
       Set ws = Sheets(sh)
    On Error GoTo 0

    If Not ws Is Nothing Then DoesSheetExists = True
End Function

Sub Check()
     Dim szToday As String
     szToday = Format(Date, "d mmm yyyy")
    

    If DoesSheetExists(szToday) Then
        MsgBox "Sheet " & szToday & " does exists"
     Else
         Call Module18.BlankSheet03
         MsgBox "Sheet" & szToday & " Created"
    End If
End Sub
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Try:
VBA Code:
Sub Check()
    Application.ScreenUpdating = False
    Dim szToday As String
    szToday = Format(Date, "d mmm yyyy")
    If Not Evaluate("isref('" & szToday & "'!A1)") Then
        Call Module18.BlankSheet03
        MsgBox "Sheet " & szToday & " created."
    Else
        MsgBox "Sheet " & szToday & " exists."
    End If
    Application.ScreenUpdating = True
End Sub
The function is not needed.
 
Upvote 0
Try:
VBA Code:
Sub Check()
    Application.ScreenUpdating = False
    Dim szToday As String
    szToday = Format(Date, "d mmm yyyy")
    If Not Evaluate("isref('" & szToday & "'!A1)") Then
        Call Module18.BlankSheet03
        MsgBox "Sheet " & szToday & " created."
    Else
        MsgBox "Sheet " & szToday & " exists."
    End If
    Application.ScreenUpdating = True
End Sub
The function is not needed.
Hi mumps,
When executing, (MsgBox "Sheet " & szToday & " exists.") still appears first before new sheet is created then another msgbox shown after sheet is created !
 
Upvote 0
Hi Vincent88,

with the code you posted as well with that of mumps only one message box will pop up. Maybe you have a look at your
Module18.BlankSheet03 as the additional MsgBox might be called from there.

And I personally would get rid of any message box which is modal and will halt the execution of the code till answered. Your aim is to create a sheet named with the date given. Check for the existence: if it´s not there the code should create this sheet. If it already exists no action is required. Without any information the result will be the same after the macro execution that a worksheet with the given name exists. I would recommend to use a message box if the sheet could not be created.

Ciao,
Holger
 
Upvote 0
Please post the code in Module18.
 
Upvote 0
Hi Vincent88,

with the code you posted as well with that of mumps only one message box will pop up. Maybe you have a look at your
Module18.BlankSheet03 as the additional MsgBox might be called from there.

And I personally would get rid of any message box which is modal and will halt the execution of the code till answered. Your aim is to create a sheet named with the date given. Check for the existence: if it´s not there the code should create this sheet. If it already exists no action is required. Without any information the result will be the same after the macro execution that a worksheet with the given name exists. I would recommend to use a message box if the sheet could not be created.

Ciao,
Holger

Please post the code in Module18.
VBA Code:
Sub BlankSheet()
  Dim ws As Worksheet
  Dim LastColumn As Long
  Dim strSheetName As String

  If ActiveWorkbook Is ThisWorkbook Then
    Set ws = ActiveSheet
 
    LastColumn = ws.Range("A1").CurrentRegion.Columns.Count
 
    On Error Resume Next
    Application.DisplayAlerts = False
  ''  ws.Range(Cells(1, 1), Cells(1, LastColumn)).Copy
    ws.Copy before:=ActiveSheet
    strSheetName = Format(Date, "d mmm yyyy")
    ActiveSheet.Name = strSheetName
    Application.DisplayAlerts = True
 
    'Clear All Contents
    With ActiveSheet
      .Cells.ClearContents
      With .OLEObjects
        .Visible = True
        .Delete
      End With
      With .Pictures
        .Visible = True
        .Delete
      End With
      .Range("A1").Resize(1, LastColumn).Value = ws.Range("A1").Resize(1, LastColumn).Value
      .ListObjects.Add(xlSrcRange, .Range("A1").Resize(2, LastColumn), , xlYes).Name = strSheetName
      .ListObjects(strSheetName).TableStyle = "TableStyleMedium2"
      .ListObjects(strSheetName).ShowAutoFilterDropDown = False
    End With
    Set ws = Nothing
  End If

End Sub
Meet Google Drive – One place for all your files

Hi, here is the code and the link to view the file
 
Upvote 0
Hi Vincent88,

with the code you posted as well with that of mumps only one message box will pop up. Maybe you have a look at your
Module18.BlankSheet03 as the additional MsgBox might be called from there.

And I personally would get rid of any message box which is modal and will halt the execution of the code till answered. Your aim is to create a sheet named with the date given. Check for the existence: if it´s not there the code should create this sheet. If it already exists no action is required. Without any information the result will be the same after the macro execution that a worksheet with the given name exists. I would recommend to use a message box if the sheet could not be created.

Ciao,
Holger
Hi HaHoBe,
I modified the code but the issue yet resolved. Below is the link of my file. See if you can help me to fix the bug.
Meet Google Drive – One place for all your files
 
Upvote 0
Your link takes me to the sign-in page. We need a direct link to your file.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top