Renaming worksheet by a specific cell

[jojoluv377]

I know this is possible I just am not that good at VB.

I've created a workbook and with 32 worksheets and now they are all named Sheet 1 (1,2,3...). What I would like to do is write a macro that would rename each worksheet to the data in cell G1. I think there should be an index worsheet, but I just don't know enough to make it all work.

TYIA

 

[VoG]

Try

Sub Namesheet()
Dim i As Long
For i = 1 To Worksheets.Count
    With Worksheets(i)
        .Name = .Range("G1").Value
    End With
Next i
End Sub

 

[jojoluv377]

Try

Sub Namesheet()
Dim i As Long
For i = 1 To Worksheets.Count
    With Worksheets(i)
        .Name = .Range("G1").Value
    End With
Next i
End Sub

Does it matter if there is a formula in cell G1? And, the data in the cell is a date. I'm sorry I failed to mention that earlier.

I ran Debug and received the following error Run-time error '1004' You typed an invalid name for a sheet or chart... So I decided to copy/paste as value to cell H1 and change the date format to mm-dd-yy. When I did that the error message I received was Run-time error '1004' Applicatio-defined or object-defined error.

Again, TIA

 

[VoG]

Try

Sub Namesheet()
Dim i As Long
For i = 1 To Worksheets.Count
    With Worksheets(i)
        .Name = Format(.Range("G1").Value, "mm/dd/yyyy")
    End With
Next i
End Sub

 

[jojoluv377]

Try

Sub Namesheet()
Dim i As Long
For i = 1 To Worksheets.Count
    With Worksheets(i)
        .Name = Format(.Range("G1").Value, "mm/dd/yyyy")
    End With
Next i
End Sub

That worked. All I had to tweak was the "mm-dd-yy" since worksheets names cannot contain /.

I appreciate your help. Thank you so much

 

[FAM]

dear
I have workbook contain 50 sheets with various name.
I need to rename all next 49 tabs.
All names are exist in sheet1 (very first sheet) in A2:A50



can any body help me to solve this out with vb

i am new to vb

thnkx
fam

 

[jproffer]

FAM,
You should probably start a new thread...

But to answer, I don't think you can change a sheet name automatically without VBA.

 

[cstimart]

dear
I have workbook contain 50 sheets with various name.
I need to rename all next 49 tabs.
All names are exist in sheet1 (very first sheet) in A2:A50



can any body help me to solve this out with vb

i am new to vb

thnkx
fam

Sub FOO()
Dim i As Byte

For i = 2 To 50
    Sheets(i).Name = Sheets(1).Cells(i, 1).Value
Next i
End Sub

 

[FAM]

dear cstimart

the code you provided I have put this in the following way but it didnt work


1. Open the workbook that has 50 worksheet in it.
2. Right-click the worksheet1 (very first sheet) tab and select View Code
from the resulting Context menu. Excel displays the VBA Editor.
3. Paste (or type) the above macro into the code window.
4. Close the VBA Editor.

 

[cstimart]

dear cstimart

the code you provided I have put this in the following way but it didnt work


1. Open the workbook that has 50 worksheet in it.
2. Right-click the worksheet1 (very first sheet) tab and select View Code
from the resulting Context menu. Excel displays the VBA Editor.
3. Paste (or type) the above macro into the code window.
4. Close the VBA Editor.

In the VBA Editor, use F5 to execute the code.