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 workshe...

Macros and VBA

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

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

Sumit_Bhokare
Brass Contributor
Apr 17, 2023
HansVogelaar Thank you for reply! It works for me 🙂

Forum Discussion

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

Resources