Forum Discussion

anupambit1797's avatar
anupambit1797
Steel Contributor
Oct 20, 2023

Multiple-Worksheets creation

Dear Experts,

                    Greetings!

I have a case where , in the main sheet I have multiple unique entries in rows and want to create seperate worksheets for each of these rows( say abc, def etc.. these rows can go to 100s) creating worksheet for each of them is very tedious could you please share some trick where I can achieve this quickly?

 

Thanks in Advance,

Br,

Anupam

excel
Formulas and Functions

10 Replies

Sort By
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 20, 2023

    anupambit1797 

    A slight variation of Patrick2788's code:

     

    Sub CreateSheetsFromNames()
    'Create new sheets from the names added
    'to column A beginning at A2 and down.
    'Sheets added left-to-right.

    Dim cel As Range, rng As Range
    Application.ScreenUpdating = False
    Set rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    For Each cel In rng
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = cel.Value
    Next cel
    Application.ScreenUpdating = True
End Sub

     

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Oct 20, 2023

    anupambit1797 

    I'm not even a vba guy but this may work for you. I'll bet someone will offer something sleeker.

     

     

    Sub CreateSheetsFromNames()
'Create new sheets from the names added
'to column A beginning at A2 and down.
'Sheets added left-to-right.

Dim cel As Range, rng As Range
Dim lngLastRow As Long

lngLastRow = Range("A65000").End(xlUp).Row

Set rng = Range("A2:A" & lngLastRow)

For Each cel In rng
    Sheets.Add After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)
    ActiveSheet.Name = cel
Next cel

End Sub

     

Resources