New Sheet naming

FenelonPaul · #1 09-23-2015, 08:23 AM

I am trying to record a macro that deletes a sheet and creates a new one.

The problem is that excel renames sheets.. if I create a new one the first time, it calls it sheet1; if I delete it and create a new one again, it calls it sheet2.

This really messes up the macro, and it mis-references the sheet because the name has changed. I can't even rename the sheet, because it can't find the original sheet1.

Is there any way around this?

NoSparks · #2 09-23-2015, 09:23 AM

The instant the new sheet is created it is the activesheet so on the next line of code
activesheet.name = "Sheet1" should rename it to Sheet1.

Myself, probably wouldn't delete the original sheet, would use .cells.delete to just clear everything from the sheet. It would then be blank same as the added sheet would be.

Seeing you're using the macro recorder this would be clicking the square above the row numbers which selects all cells then hitting delete.

charlesdh · #3 09-23-2015, 11:02 AM

Hi,

Here's a simple code to add and name a worksheet also delete the named worksheet.
Place this code in a module. Test on sample sheet ony.

Code:

Sub AddWorksheet()
Application.DisplayAlerts = False'' this will prevent warning about deleting the sheet
Sheets("Your sheet name").Delete
Application.DisplayAlerts = True ''this will reset the warning

Worksheets.Add().Name = "MySheet"''' change "MySheet to the name you want

End Sub

FenelonPaul · #4 09-24-2015, 07:57 AM

Thanks, Charles.. that works perfectly for me!

charlesdh · #5 09-24-2015, 09:44 AM

Thanks for the update. You can mark this as solved.

FenelonPaul · #6 09-24-2015, 10:28 AM

Quote:

Originally Posted by charlesdh

Thanks for the update. You can mark this as solved.

uh.. ok.. how?

charlesdh · #7 09-24-2015, 10:33 AM

Hi,

Click on "Thread Tools". You will see it.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Batch re-naming	twols26	Word VBA	7	06-16-2015 12:29 PM
Populate sheet 3 with data from sheet 1 and sheet 2	speck	Excel Programming	0	01-14-2015 07:54 AM
Naming Cells for formula referencing	lynchbro	Excel	6	06-26-2014 07:45 AM
re-naming arrays in VBA?	JDevsFan	Excel Programming	4	03-15-2012 08:44 AM
Auto file naming	Rong Peng	Word VBA	0	07-29-2011 07:37 AM