Results 1 to 7 of 7

VBA - Adding a New Worksheet, Naming with Cell Reference in Source Sheet

This is a discussion on VBA - Adding a New Worksheet, Naming with Cell Reference in Source Sheet within the Excel Questions forums, part of the Question Forums category; Hello- Here's what I'm trying to do.... I have ~2250 names in SheetA, Column A (Cells A1:A2250). I need VBA ...

  1. #1
    New Member
    Join Date
    Jul 2013
    Posts
    9

    Default VBA - Adding a New Worksheet, Naming with Cell Reference in Source Sheet

    Hello-

    Here's what I'm trying to do....

    I have ~2250 names in SheetA, Column A (Cells A1:A2250). I need VBA to add 2250 worksheets for each of the names in the Column. When it adds the new worksheet I need cell B7 in each of the new worksheets filled by with the names from cell A1 then A2 then A3 and so on for every sheet that is added. Additionally, it would be great to have the sheet names also equal cell B7.

  2. #2
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,277

    Default Re: VBA - Adding a New Worksheet, Naming with Cell Reference in Source Sheet

    Welcome to the Board!

    Here's one way:

    Sub AddSheets()
    ****Dim c As Range
    ****
    ********For Each c In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    ************Worksheets.Add After:=Sheets(Sheets.Count)
    ************With ActiveSheet
    ****************.Name = c.Value
    ****************.Range("B7").Value = c.Value
    ************End With
    ********Next c
    ********
    End Sub


    Note that the code doesn't take potential duplicates names into account, in which case it will bomb. If there can be duplicates, you'll need to indicate how you want to manage that.

    HTH,
    Smitty

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

  3. #3
    New Member
    Join Date
    Jul 2013
    Posts
    9

    Default Re: VBA - Adding a New Worksheet, Naming with Cell Reference in Source Sheet

    Quote Originally Posted by jcoleman5534 View Post
    Hello-

    Here's what I'm trying to do....

    I have ~2250 names in SheetA, Column A (Cells A1:A2250). I need VBA to add 2250 worksheets for each of the names in the Column. When it adds the new worksheet I need cell B7 in each of the new worksheets filled by with the names from cell A1 then A2 then A3 and so on for every sheet that is added. Additionally, it would be great to have the sheet names also equal cell B7.
    Ok, so if I want to perform the same function except applying formatting of a current sheet with the macro that adds the new sheets how do I implement?

  4. #4
    New Member
    Join Date
    Jul 2013
    Posts
    9

    Default Re: VBA - Adding a New Worksheet, Naming with Cell Reference in Source Sheet

    In other words, I have a sheet that is formatted a certain way that I just want to create copies of with the new list of names input rather than simply copied.

    Does that make sense?

  5. #5
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,277

    Default Re: VBA - Adding a New Worksheet, Naming with Cell Reference in Source Sheet

    Quote Originally Posted by jcoleman5534 View Post
    In other words, I have a sheet that is formatted a certain way that I just want to create copies of with the new list of names input rather than simply copied.

    Does that make sense?
    You can use:

    Sheets("Master").Copy After:=Sheets(Sheets.Count)

    Just note that there is a known bug in Excel that it will hiccup after programmatically copying x number of sheets. Microsoft documents a workaround here: Copying worksheet programmatically causes run-time error 1004 in Excel
    Smitty

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

  6. #6
    New Member
    Join Date
    Jul 2013
    Posts
    9

    Default Re: VBA - Adding a New Worksheet, Naming with Cell Reference in Source Sheet

    So where do I insert that into the original code?


    Quote Originally Posted by Smitty View Post
    You can use:

    Sheets("Master").Copy After:=Sheets(Sheets.Count)

    Just note that there is a known bug in Excel that it will hiccup after programmatically copying x number of sheets. Microsoft documents a workaround here: Copying worksheet programmatically causes run-time error 1004 in Excel

  7. #7
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,277

    Default Re: VBA - Adding a New Worksheet, Naming with Cell Reference in Source Sheet

    Replace the Worksheets.Add portion with it.

    Note that you'll need to change the "Master" sheet name as it relates to your workbook.
    Smitty

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com