PDA

View Full Version : Rename sheets from cell in active sheet



spflash
05-26-2006, 10:08 AM
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

:banghead:

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

austenr
05-26-2006, 10:19 AM
Try this:


ThisWorkbook.SaveAs Range("Your cell here").Value & ".xls"

spflash
05-26-2006, 11:35 AM
austenr,

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

lucas
05-26-2006, 12:08 PM
this goes in the code for the worksheet you wish to change the name on and works on any change in the sheet:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$A$1" Then
Me.Name = Range("A1").Value
End If
End Sub


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

spflash
05-31-2006, 06:10 AM
lucas,

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