Renaming and Deleting an Existing Worksheet Using VBA

[aryaden]

I currently have multiple workbooks with two worksheets, I would like to delete one and rename the other to the name of the deleted one.

For example I have "Data" and "CollectedData" I would like to delete "Data" and rename "CollectedData" to "Data". I am trying to make a macro as all the workbooks I have contain the same naming convention for the worksheets and I am already running a set of macros on the worksheets.

Thanks for all the help in advance!

 

[HaHoBe]

Hi aryaden,

maybe try it like this (this works for the active workbook):

Sub CheckDeleteAndRename()
'https://www.mrexcel.com/board/threads/renaming-and-deleting-an-existing-worksheet-using-vba.1175859/

Const cstrDATA As String = "Data"
Const cstrCOLLDATA As String = "CollectedData"

Application.EnableEvents = False
If Evaluate("ISREF('" & cstrDATA & "'!A1)") Then
  Application.DisplayAlerts = False
  Sheets(cstrDATA).Delete
  Application.DisplayAlerts = True
End If
If Evaluate("ISREF('" & cstrCOLLDATA & "'!A1)") Then
  Sheets(cstrCOLLDATA).Name = cstrDATA
End If
Application.EnableEvents = True

End Sub

Any sheet inside the workbook named Data will be deleted, and any sheet CollecedData renamed to Data.

Ciao,
Holger

 

[aryaden]

Hi aryaden,

maybe try it like this (this works for the active workbook):

Sub CheckDeleteAndRename()
'https://www.mrexcel.com/board/threads/renaming-and-deleting-an-existing-worksheet-using-vba.1175859/

Const cstrDATA As String = "Data"
Const cstrCOLLDATA As String = "CollectedData"

Application.EnableEvents = False
If Evaluate("ISREF('" & cstrDATA & "'!A1)") Then
  Application.DisplayAlerts = False
  Sheets(cstrDATA).Delete
  Application.DisplayAlerts = True
End If
If Evaluate("ISREF('" & cstrCOLLDATA & "'!A1)") Then
  Sheets(cstrCOLLDATA).Name = cstrDATA
End If
Application.EnableEvents = True

End Sub

Any sheet inside the workbook named Data will be deleted, and any sheet CollecedData renamed to Data.

Ciao,
Holger

Thank you that worked!