+ Reply to Thread
Results 1 to 7 of 7

How to Create New Sheets by Cycling Through Drop-Down List

  1. #1
    Registered User
    Join Date
    01-23-2014
    Location
    United States
    MS-Off Ver
    Excel 2016 - Windows
    Posts
    20

    How to Create New Sheets by Cycling Through Drop-Down List

    I have a file that create a "cover page" that summarizes information based on a big database. Each "cover page" is for a different city, and I have those cities in a dropdown list (data validation style). Could someone help me -

    1. Run Macro to create a new sheet for each city in the list (list is located in cell A2)
    2. Rename each new sheet as the city name (located in cell A2)

    screen refreshing should be off since it's a big file - that i've pulled all the data out of for the sake of simplicity

    Extra special bonus

    1. Could each of those newly created sheets be hardcoded once they are run?
    2. Could each new hardcoded sheet for each city then be moved to a new workbook for easy saving/printing?

    Thanks all - I know it's a lot, and way beyond my current skill level.

    CreateNamedSheetsDropDown.xlsx

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: How to Create New Sheets by Cycling Through Drop-Down List

    Hello cchase22,

    Your drop down currently changes the name on "Sector Template" worksheet. The data is all static on the sheet.

    What code or program updates this sheet's data when the company name is changed?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    01-23-2014
    Location
    United States
    MS-Off Ver
    Excel 2016 - Windows
    Posts
    20

    Re: How to Create New Sheets by Cycling Through Drop-Down List

    I took out the other sheets that have all of the data - and hard coded all of the formulas so that i wasn't uploading a bulky file. The data for the tables and graphs are all on other sheets and using lookup formulas to retrieve - based on the name of the City name that is selected in cell A2. Instead of having to click every city in the drop down and print that page, i'm hoping to cycle through each of the cities, make that a new sheet, and then do the same for the next city in the list.

    Does that clarify at all?

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: How to Create New Sheets by Cycling Through Drop-Down List

    Hello Hello cchase22,

    Are you saying that data for each city is on a separate sheet in the same workbook?

  5. #5
    Registered User
    Join Date
    01-23-2014
    Location
    United States
    MS-Off Ver
    Excel 2016 - Windows
    Posts
    20

    Re: How to Create New Sheets by Cycling Through Drop-Down List

    No - data for all of cities are on the same two sheets. I've added a new file for clarity - sorry for the confusion, I thought leaving out the data would make it easier to understand. Thanks for bearing with me on this one.

    I left only the data for Atlanta and Boston. (Big file)
    v2.SectorTemplateHelp.xlsx

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: How to Create New Sheets by Cycling Through Drop-Down List

    Hello cchase22,

    Thanks for the new workbook. Makes sense now.

    Not sure what you mean by hard coding the sheets. Do want the data shown to travel with sheet and not be linked to the template worksheet?

  7. #7
    Registered User
    Join Date
    01-23-2014
    Location
    United States
    MS-Off Ver
    Excel 2016 - Windows
    Posts
    20

    Re: How to Create New Sheets by Cycling Through Drop-Down List

    I want basically 26 sheets (one for each city) . They can honestly go either of two ways in my mind 1. stay linked to the original sheets 2. be pasted as values in their new locations.

    By hard coding, I meant copy and re-pasting all the cells as value so they don't change. I think this would be ideal for so we don't run into issues with sheets referencing other workbooks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to create a drop down list based on the choices of another drop down list?
    By Happy jain in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-27-2013, 01:54 AM
  2. Create a drop down list which is made up of the other sheets
    By usrobinjo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-13-2012, 09:33 PM
  3. Replies: 1
    Last Post: 10-10-2012, 07:09 AM
  4. Cycling through a list of Names and repeating actions
    By BeefNoodleSoup in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-27-2011, 04:13 AM
  5. Create a Drop down list based on another drop down list value
    By pdewet in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-26-2010, 06:54 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1