auto name sheet w/ macro or function

[laneman]

I want to fill in the name of the sheet tab for each new sheet with som
automated feature, either a macro or a formula. One cell in each shee
has the name already, so it can be copied and pasted as part of th
macro. I tried a macro but got errors. Can this be done

 

[Bob Phillips]

sFile = Activesheet.Name

For Each sh In Activeworkbook.Worksheets
If sh.Name <> Activesheet.Name Then
i = i + 1
sh.Name = sFile & "_" & i
End If
Next sh

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[laneman]

Thanks for the reply Bob,but this is not really what I am trying to do
If cell H3 contents = 12345 I want to tell a macro to paste the valu
of H3 into the sheet tab name

 

[Bob Phillips]

Sorry, I read cell as file. Try this

For Each sh In Activeworkbook.Worksheets
sh.Name = sh.Range("H3").Value
Next sh


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[laneman]

Bob, it works now, but after the macro runs I get a Runtime error '1004
Appliction defined orobject defined error. When I checked the debugge
the line [sh.Name = sh.Range("H3").Value] is highlighted. Any way t
get rid of this pop-up

 

[Bob Phillips]

That is odd, if it runs why does an error occur.

What does your full code look like?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[laneman]

Full code below... I guess I could add a line that types the letter "e"
which will end the Pop-up message.

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 7/8/2004 by Laneman
'
' Keyboard Shortcut: Ctrl+t
For Each sh In ActiveWorkbook.Worksheets
sh.Name = sh.Range("H3").Value
Next sh

End Su