Macro to name sheets sudden;y stopped working

[Shiremaid]

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.

    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.

 

[mumps]

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.

 

[Shiremaid]

So Can I just remove the "For Each" and "next" lines to make it rename only the active sheet?

 

[mumps]

When you create a new sheet, it automatically becomes the active sheet. So if you use a line such as

[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

[FONT=Courier New][COLOR=#0000bb]Sheets("Sheet1"[/COLOR][/FONT])

to the name of the sheet from which you are running the macro.

 

[stefankemp]

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.

 

[Shiremaid]

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!

 

[Jonmo1]

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.