Share via

Macro to create a new tab and then name it from a list

Anonymous
2022-12-12T21:23:44+00:00

I need a macro that will create a new tab and then name that new tab from a list of approximately 200 lines.

Start at line 1 of the summary; create a new tab and name it APPLE.

Go to line 2 of the summary; create a new tab and name it BANANA.

Go to line 3 of the summary; etc., etc., etc...........................

Go to line 200 of the summary; create a new tab and name it ZEBRA

End

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2022-12-12T22:01:28+00:00

    Hi,

    List names (in column A) in sheet Sheet1

    Note: make a copy before you run this code

    Sub Add_new_shts()

    Const shName = "Sheet1" '<< source sheet name /change as needed

    Dim ws, sh, r

    Set ws = Worksheets(shName)

    'delete old shts

    Application.DisplayAlerts = False

    For Each sh In ThisWorkbook.Sheets

    If sh.Name <> shName Then

    sh.Delete

    End If

    Next sh

    Application.DisplayAlerts = True

    '

    'add new sheets

    For Each r In Sheets(shName).Range("A:A").SpecialCells(xlCellTypeConstants)

    Set sh = Sheets.Add(after:=Sheets(Sheets.Count))

    sh.Name = r.Value

    Next r

    Sheets(1).Select

    End Sub

    1 person found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-12-24T19:31:48+00:00

    AnastasiosGR....... This worked great....... Thank you so much

    Reply

    0 comments
  2. Anonymous
    2022-12-15T07:38:32+00:00

    Sneha....... thank you for responding.... This did create the tabs as needed however, they were in reverse alphabetical order

    i.e. Zebra was left most, Yellow was next on the right, etc., etc..................... Apple was all the way on the right

    0 comments
  3. Anonymous
    2022-12-15T07:27:35+00:00

    AnastasiosGR....... This worked great....... Thank you so much

    0 comments
  4. Anonymous
    2022-12-13T00:34:23+00:00

    Hi Polimpio,

    I'm Sneha and I'd be happy to help you out with your question. To create a macro that will create a new tab and name it based on a list of values, you can use the Add and Name methods of the Worksheets object in the spreadsheet program's scripting language.

    Here is an example of a macro that you can use to create a new tab and name it based on a list of values:

    Sub CreateTabs()

    Dim summarySheet As Worksheet Dim summaryRange As Range Dim cell As Range

    Set summarySheet = Worksheets("Summary") Set summaryRange = summarySheet.Range("A1:A200")

    For Each cell In summaryRange Worksheets.Add ActiveSheet.Name = cell. Value Next cell

    End Sub

    In this macro, Summary is the name of the sheet that contains the list of values, and A1:A200 is the range of cells that contains the values. The macro will loop through each cell in this range, create a new tab, and name it based on the value in the cell.

    You can customize this macro to suit your specific needs, such as changing the sheet and range references to match your data, or modifying the code to perform other actions on the newly-created tabs.

    If you have any other questions or need assistance with anything, please don't hesitate to let me know. I'm here to help to the best of my ability.

    Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below.

    Best Regards, Sneha

    0 comments