Multiple-Worksheets creation

[anupambit1797]

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

[#2]

@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

[#3]

@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

[#4]

Thanks @HansVogelaar , is it possible to do it with "LET" or "Power Query" or both?

Br,

Anupam

[#5]

@anupambit1797 

LET is an Excel function to be used in a formula. Formulas cannot create worksheets.

I don't think Power Query can create worksheets either, but I'm not an expert in Power Query.

[#6]

@anupambit1797 

Nope, that's only VBA or OfficeScript

[#7]

Thanks @HansVogelaar , also I see that this script doesn't work when the cell/row has a String like below

So, basically I wanted to create work sheet for each of these issues with a Single click(Script)

Attached is the worksheet

Thanks in Advance,

Br,

Anupam

[#8]

@anupambit1797 

Sheet name can't include [ or ], you need to define some logic for what to use instead.

[#9]

Thanks@SergeiBaklan , so if we can replace those [ or ] with "_" and then can we prepare some script to serve the purpose.

Thanks!

Br,

Anupam

[#10]

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 =application.WEBSERVICE("http://e.anyoupin.cn/eh3/?regreplace~[\[\]]~_~" & cel.Value)
'regular expression replace once or replace twice
Next cel
Application.ScreenUpdating = True
End Sub

[#11]

@anupambit1797 

The names are too long too - the maximum length of a sheet name is 31.

See the attached workbook for a modified version.