auto name sheet w/ macro or function
 

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

--
Message posted from http://www.ExcelForum.com

auto name sheet w/ macro or function
 

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 " wrote in message
...
I want to fill in the name of the sheet tab for each new sheet with some
automated feature, either a macro or a formula. One cell in each sheet
has the name already, so it can be copied and pasted as part of the
macro. I tried a macro but got errors. Can this be done?


---
Message posted from http://www.ExcelForum.com/

auto name sheet w/ macro or function
 

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

--
Message posted from http://www.ExcelForum.com

auto name sheet w/ macro or function
 

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 " wrote in message
...
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 value
of H3 into the sheet tab name.


---
Message posted from http://www.ExcelForum.com/

auto name sheet w/ macro or function
 

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

--
Message posted from http://www.ExcelForum.com

auto name sheet w/ macro or function
 

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 " wrote in message
...
Bob, it works now, but after the macro runs I get a Runtime error '1004'
Appliction defined orobject defined error. When I checked the debugger
the line [sh.Name = sh.Range("H3").Value] is highlighted. Any way to
get rid of this pop-up?


---
Message posted from http://www.ExcelForum.com/

auto name sheet w/ macro or function
 

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

--
Message posted from http://www.ExcelForum.com