Renaming and Deleting an Existing Worksheet Using VBA

aryaden

Board Regular
Joined
Jun 9, 2021
Messages
101
Office Version
  1. 2019
Platform
  1. Windows
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!
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

HaHoBe

Active Member
Joined
Jan 24, 2003
Messages
490
Office Version
  1. 2013
Platform
  1. Windows
Hi aryaden,

maybe try it like this (this works for the active workbook):
VBA Code:
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
 
Solution

aryaden

Board Regular
Joined
Jun 9, 2021
Messages
101
Office Version
  1. 2019
Platform
  1. Windows
Hi aryaden,

maybe try it like this (this works for the active workbook):
VBA Code:
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!
 

Forum statistics

Threads
1,141,707
Messages
5,707,980
Members
421,539
Latest member
zuniBM

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
Top