SOLVED

How to rename multiple worksheets using cell value of single column.

Brass Contributor

All,

need one help on renaming of worksheet in same workbook.

I have sample template form & I want to make copy of those form & need to rename each sheet based on value available in another worksheet column.

Sheet1 have required names in range A1:A10

Sheet2 is required form.

need to create 10 copies of Sheet2 and rename those as per value available in range A1:A10.

2 Replies
best response confirmed by Sumit_Bhokare (Brass Contributor)
Solution

@Sumit_Bhokare 

Change Sheet1 and Sheet2 to the real names of your worksheets.

Sub CopySheet()
    Dim rng As Range
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    For Each rng In Worksheets("Sheet1").Range("A1:A10")
        Worksheets("Sheet2").Copy After:=Worksheets(Worksheets.Count)
        Worksheets(Worksheets.Count).Name = rng.Value
    Next rng
ExitHandler:
    Application.ScreenUpdating = True
    Exit Sub
ErrHandler:
    MsgBox rng.Value & " is not a valid name!", vbExclamation
    Resume Next
End Sub

@HansVogelaar Thank you for reply! It works for me :)

1 best response

Accepted Solutions
best response confirmed by Sumit_Bhokare (Brass Contributor)
Solution

@Sumit_Bhokare 

Change Sheet1 and Sheet2 to the real names of your worksheets.

Sub CopySheet()
    Dim rng As Range
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    For Each rng In Worksheets("Sheet1").Range("A1:A10")
        Worksheets("Sheet2").Copy After:=Worksheets(Worksheets.Count)
        Worksheets(Worksheets.Count).Name = rng.Value
    Next rng
ExitHandler:
    Application.ScreenUpdating = True
    Exit Sub
ErrHandler:
    MsgBox rng.Value & " is not a valid name!", vbExclamation
    Resume Next
End Sub

View solution in original post