Rename sheets from cell in active sheet

[spflash]

Hi All,

Using the code below I'm attempting to rename my worksheet by using a string (or number) contained in cell E4 for example that's present on the same active sheet. The loop steps through a pivot table to create a new sheet for each consecutive cell selected. After each new sheet is created, I'd like to rename it using the string (or number) contained in cell "E4". So far I've had no success with the code below. Can anyone help me with this stumbling block? Thanks.

spflash


[VBA]
Sub PivotTable
Dim TB As Worksheet
Dim FinalRow As Long
ActiveSheet.Name = "PivotTable"
For i = 5 To Final Row
Cells(i, 3).Select
Selection.ShowDetail = True
Active Sheet.Name=TB.Range ("E4")
Sheets ("PivotTable").Select
Next i
End Sub
[/VBA]

[austenr]

Try this:


[VBA] ThisWorkbook.SaveAs Range("Your cell here").Value & ".xls" [/VBA]

[spflash]

austenr,

Thank you for your help. It is greatly appreciated. But how do I apply this
piece of code in my macro.

[lucas]

this goes in the code for the worksheet you wish to change the name on and works on any change in the sheet:
[VBA]
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$A$1" Then
Me.Name = Range("A1").Value
End If
End Sub
[/VBA]

whatever you put in cell A1 will be the name of the sheet. Maybe it will get you a start.

[spflash]

lucas,

Thank you for your input. I greatly appreciate the help.
I will try the code A.S.A.P.