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

[Sumit_Bhokare]

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 · Accepted 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

[#3]

@Hans Vogelaar Thank you for reply! It works for me :)