Forum Discussion

Sumit_Bhokare's avatar
Sumit_Bhokare
Copper Contributor
Apr 17, 2023
Solved

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

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.

Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Apr 17, 2023

    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

2 Replies

Sort By
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Apr 17, 2023

    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

Resources