renaming sheets dinamically

[pimar]

Hi all.

In my spreadsheet, I'd like to have some sheets (not all) which name is dependent from the value of a cell inthe main sheet..
For example,
if cell A1 of sheet "Main" is equal to "Stage 1",
I'd like to have another sheet to be named "summary for Stage 1"..
if I'd change the A1 cell in "Stage A", then the sheet name should change in "summary for Stage A"
I think I need a macro for this, but I don't know where to start from..
any help is really appreciated..
thanks

[Norman Jones]

Hi Pimar,

Try:
'==================>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Const sStr As String = "Summary for "

Set rng = Range("A1")

On Error Resume Next
If Not Intersect(Target, rng) Is Nothing Then
If Not IsEmpty(rng) Then
Sheet2.Name = sStr & rng.Value
End If
End If
On Error GoTo 0

End Sub
'<<==================

This is worksheet event code and should be pasted into the worksheets's code
module (not a standard module and not the workbook's ThisWorkbook module):

*******************************************
Right-click the worksheet's tab

Select 'View Code' from the menu and paste the code.

Alt-F11 to return to Excel.
*******************************************


---
Regards,
Norman



"pimar" <[email protected]> wrote in
message news:[email protected]...
>
> Hi all.
>
> In my spreadsheet, I'd like to have some sheets (not all) which name is
> dependent from the value of a cell inthe main sheet..
> For example,
> if cell A1 of sheet "Main" is equal to "Stage 1",
> I'd like to have another sheet to be named "summary for Stage 1"..
> if I'd change the A1 cell in "Stage A", then the sheet name should
> change in "summary for Stage A"
> I think I need a macro for this, but I don't know where to start
> from..
> any help is really appreciated..
> thanks
>
>
> --
> pimar
> ------------------------------------------------------------------------
> pimar's Profile:
> http://www.excelforum.com/member.php...fo&userid=5386
> View this thread: http://www.excelforum.com/showthread...hreadid=474385
>

[pimar]

Thank you, Norman
I'll try it and let you know ...
*pimar

[pimar]

Norman,
it works fine, great.
Could you help me to make it more flexible, since I'm not very familiar with this scripting?
In sense that I need to rename more than 1 sheet based on a range of values..
These are the changes I need to make (see my comments)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Const sStr As String = "Summary for "

// Range is from A1 to A4
Set rng = Range("A1","A2","A3","A4")

On Error Resume Next
For count=0 to 3 //4 sheets to rename

If Not Intersect(Target, rng) Is Nothing Then
If Not IsEmpty(rng) Then
// Question: how can I "combine" the name of the sheet based on value of count
Sheet???.Name = sStr & rng.Value
End If
End If
End For

On Error GoTo 0

End Sub

Thanks very much for your help..
pimar