Excel Wookbook naming

[Digdug]

I'm trying to change the name of a worksheet by using information typed into
a cell on that worksheet, ie cell C4. I have several worksheets that I want
to apply this to in the same workbook. I have tried a macro....

Public Sub RenameSheet()
NewName = Range("C4").Value
ActiveSheet.Name = NewName
End Sub

but I cannot get this macro to work across several worksheets. I also need
this to work when the workbook is protected, which I do not think can be
done.

Is there an easier way to accomplish this, maybe by using a formula?

Thanks

 

[Don Guillett]

From another workbook, have your macro unprotect the desired wb>run a for
each loop>re-protect
for each ws in worksheets
ws.name=range("c4")
next ws

 

[JulieD]

Hi

can't use a formula to do this, you'll need to use code - e.g.

Sub changenames()
ActiveWorkbook.Unprotect "pwd"
For Each ws In Worksheets
ws.Name = ws.Range("C4").Value
Next
ActiveWorkbook.Protect "pwd"
End Sub

Note: if you're using a password, put it in place of "pwd", if not delete
"pwd"

If you have worksheets that you don't want to rename you'll need to add an
IF statement in - if you need help with this, let us know how you identify
the ones to change as opposed to the ones you don't want to change.

Cheers
JulieD

 

[Tom Ogilvy]

Public Sub RenameSheet()
Dim NewName as String
Activeworkbook.UnProtect Password:="ABCD"
for each sheet in ActiveWorkbook.Worksheets
if sh.Range("C4").Value <> "" then
if lcase(sh.Name) <> "master" and lcase(sh.Name) <> _
"summary" then
NewName = sh.Range("C4").Value
sh.Name = NewName
End if
End if
Next
ActiveworkBook.Protect Password:="ABCD"
End Sub

As an example, I also include code to exclude two specific sheets since it
didn't sound like you want all sheets renamed.