Macro to name sheets sudden;y stopped working

Shiremaid

Board Regular
Joined
Jun 18, 2013
Messages
73
I have a workbook to record orders and when I add the current order to the history it creates a new page and then renames it to the value of B3.
It worked fine for a while, then suddenly this yaer everytime I try to add the order to history I get
Run-time error '1004': Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic

I have checked and there are no sheets named what I'm trying to name it (the date formatted as daymonthyear (10Jan14). I tried changing B3 to something else before running the macro (Test This....Tape Deck....Work Please) and still get the same error.

PHP:
    Dim ws As Worksheet
     
    For Each ws In Sheets
        ws.Name = ws.Range("B3")
    Next ws

It highlights the third line when I choose debug. So what's wrong with my code?
I have tried removing the code completely and putting it back in and get the same results.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Your code is looping through all the sheets and trying to re-name them all with the same name which happens to be the value in B3.
 
Upvote 0
When you create a new sheet, it automatically becomes the active sheet. So if you use a line such as
Code:
[FONT=Courier New][COLOR=#0000bb]Activesheet[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000bb]Name [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000bb]Sheets("Sheet1")[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000bb]Range[/COLOR][COLOR=#007700]([/COLOR][COLOR=#dd0000]"B3"[/COLOR][/FONT][COLOR=#007700][FONT=Courier New])[/FONT][/COLOR]
right after the line of code that creates the new sheet, then that sheet will be named using the value from B3. You should, of course, change the value of B3 before you create each new sheet. Change the sheet name in
Code:
[FONT=Courier New][COLOR=#0000bb]Sheets("Sheet1"[/COLOR][/FONT])
to the name of the sheet from which you are running the macro.
 
Upvote 0
Your code is looping through all the sheets and trying to re-name them all with the same name which happens to be the value in B3.

I don't think that is exactly what is happening. The code is trying to re-name each sheet to whatever is in B3 OF EACH sheet. So the error is likely occuring because there are two sheets with the same value in cell B3.
 
Upvote 0
Ok, that should work. Thanks
Ah, stefankemp, that would explain why it used to work. Must have changed something on a sheet somewhere.
Thanks guys!

:)
 
Last edited:
Upvote 0
The posted code is not using the same Cell for each sheet being renamed in the loop.
It's using B3 from each sheet during the loop

Say there are 3 sheets named Sheet1 Sheet2 and Sheet3
It renames Sheet1 to the value in B3 on Sheet1
It renames Sheet2 to the value in B3 on Sheet2
It renames Sheet3 to the value in B3 on Sheet3


The basic problem that there is already a sheet (or some other object as the error suggests) with the same name.
In the VBA, open the Locals Window
Use F8 to step through the code and look at the ws variable to see which sheet is having the problem.
 
Upvote 0

Forum statistics

Threads
1,224,454
Messages
6,178,766
Members
452,875
Latest member
Disastrouscoder

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