CODEWITHSUNDEEP
excelvba
PGS
PGS

Reputation: 23

Check if sheet exists then delete and add new one with same name

I have been trying to write some VBA code to find if sheets with specific name exist, if so, then delete them and create new sheet with that same name. My code looks like this:

Sub DeleteSheet ()
   Dim ws as worksheet

   For each ws in worksheets
       if ws.name = "test" then
           Application.DisplayAlerts = False
           ws.delete
           Application.DisplayAlerts = True
       End if
   Next ws

   Sheets.add.name = "test"
End Sub

It doesn't seem to work when I run the sub. First the sheet was deleted but it does not create the new one. I have to run it for the second time to be able to get the new sheet.

Please anyone help me on this. I would really appreciate very much. Thank you in advance.

Upvotes: 0

Views: 2322

Answers (5)

Kwan Wan Sing
Kwan Wan Sing

Reputation: 26

Try this code, you may need to insert your sheet name to delete and add a new one in this code.

Sub deletews()

Dim ws As Worksheet
Dim wsName As String
wsName = InputBox("Insert your sheet name")
Application.DisplayAlerts = False
For Each ws In Worksheets
If Sheets(wsName).name = wsName Then
Sheets(wsName).delete
Sheets.Add
ActiveSheet.name = wsName
End If
Next ws
Application.DisplayAlerts = True
End Sub

Upvotes: 0

Domenic
Domenic

Reputation: 8124

As it stands, your code refers to the active workbook. Try qualifying your Worksheets reference. For example, if the target workbook is the workbook running the macro, try...

Sub DeleteSheet()
   Dim wb As Workbook
   Dim ws As Worksheet

   Set wb = ThisWorkbook

   For Each ws In wb.Worksheets
       If ws.Name = "test" Then
           Application.DisplayAlerts = False
           ws.Delete
           Application.DisplayAlerts = True
       End If
   Next ws

   wb.Sheets.Add.Name = "test"
End Sub

To specify another workbook, replace...

Set wb = ThisWorkbook

with

Set wb = Workbooks("Book2.xlsx") 'change the workbook name accordingly

Upvotes: 0

Applecore
Applecore

Reputation: 4099

Another option is to delete the sheet, and use error handling to carry on if it doesn't exist:

Sub sDelete()
    On Error GoTo E_Handle
    Sheets("Testing").Delete
    Sheets.Add.Name = "Testing"
sExit:
    On Error Resume Next
    Exit Sub
E_Handle:
    Select Case Err.Number
        Case 9
            Resume Next
        Case Else
            MsgBox Err.Description & vbCrLf & vbCrLf & "sDelete", vbOKOnly + vbCritical, "Error: " & Err.Number
    End Select
    Resume sExit
End Sub

Regards,

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96791

Use 2 steps:

Sub DeleteSheet()
   Dim ws As Worksheet

   For Each ws In Worksheets
       If ws.Name = "test" Then
           Application.DisplayAlerts = False
           ws.Delete
           Application.DisplayAlerts = True
       End If
   Next ws

   Sheets.Add
   ActiveSheet.Name = "test"
End Sub

Upvotes: 0

braX
braX

Reputation: 11755

Instead of

Sheets.add.name = "test"

You can create the object, and then set the name:

Set ws = Sheets.add
ws.name = "test"

Or better yet:

Set ws = Worksheets.Add
ws.Name = "test"

Upvotes: 1

Related Questions