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

From another workbook, have your macro unprotect the desired wbrun a for
each loopre-protect
for each ws in worksheets
ws.name=range("c4")
next ws

--
Don Guillett
SalesAid Software

"Digdug" wrote in message
...
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

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.

--
Regards,
Tom Ogilvy


"Digdug" wrote in message
...
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

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

"Digdug" wrote in message
...
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