Change Worksheet name to reflect Cell Value

[Stephenf_07]

Hello,

I would like to have Sheet 1 in my Workbook be the main sheet that controls what the rest of the Worksheets are named in the book. Whatever is entered in Cell A1 is the name of sheet 1. Whatever is entered in Cell A2 will change the name of sheet 2 and so on. Is this possible to do with VBA? I am using Excel 2010.

Thanks in advance for any help

 

[tweedle]

something like this maybe...

Sub NameSheets()
Dim C As Range
With Sheets(1) 'Control sheet is first, yes?
 
 
    For Each C In .Range("A1:A10") '<SET p as Names Sheet of Range desired<>         
        If C.Text <> "" And C.Row <= ActiveWorkbook.Sheets.Count Then
            Sheets(C.Row).Name = Trim(C.Text)
        End If
    Next
End With
End Sub

 

[Stephenf_07]

I made this a worksheet change event and put it in sheet 2 actually. This was the code I used:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim C As Range
With Sheets(2) 'Control sheet is first, yes?


For Each C In .Range("A1:A10") '<>
If C.Text <> "" And C.Row <= ActiveWorkbook.Sheets.Count Then
Sheets(C.Row).Name = Trim(C.Text)
End If
Next
End With

End Sub



I am getting an error here:
Sheets(C.Row).Name = Trim(C.Text)

What am I doing wrong? Thanks again for helping

 

[Stephenf_07]

Sorry - it was a minor error on my end. Your code works perfectly.

Thank you very much!!!!