+ Reply to Thread
Results 1 to 8 of 8

Create new sheets by using drop/down list

  1. #1
    Forum Contributor
    Join Date
    09-10-2012
    Location
    Slovakia
    MS-Off Ver
    Excel 365
    Posts
    174

    Create new sheets by using drop/down list

    Hello,

    I would like to ask for your help in updating of following macro code used in attached example file.

    This macro will create (copy and rename sheet LOI 4.0) according to column "C" - Account Group.

    Please Login or Register  to view this content.


    However I would like to update this code as follows:

    In column "B" (sheet Analysis) you can see names of Workers and once I run macro, I would like to see some drop down list of all workers and choose/mark only those for whom should be created new sheets. I would like to make it applicable not only for choosing 1 name but also for more/all workers. Pls be aware, it is only example file with few lines, but there can be more rows.

    Thanks in advance for your help.
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Create new sheets by using drop/down list

    I need some clarification. You do not want to make sheets by name. You want to still make sheets for the account group, but only for the rows for selected workers. Is this correct?

    [Edit] On further thought, it looks like copying the contents of a pivot table would be the best approach. Set the filters and then copy the result.

  3. #3
    Forum Contributor
    Join Date
    09-10-2012
    Location
    Slovakia
    MS-Off Ver
    Excel 365
    Posts
    174

    Re: Create new sheets by using drop/down list

    Quote Originally Posted by dflak View Post
    I need some clarification. You do not want to make sheets by name. You want to still make sheets for the account group, but only for the rows for selected workers. Is this correct?
    Yes, correct.
    Current macro does what I need, but I want to update it and run it only for chosen names. My idea is, if you click on button "Create LOIs", then pop-up window shows you list of names, where you can mark which name you need and then press "OK". The rest will continue by using current macro, but only for chosen name(s).

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Create new sheets by using drop/down list

    I think I got this. I created a form with a multi-select list box. I put this behind the button to create the report. Launch the form, select the names and run the program. I feed the selected names into a globally defined array.

    I changed the data to an Excel Table. I wanted to create a pivot table, but there were so many defined ranges and merged cells, Excel couldn't find the range. This should not affect anything else you are doing. I made a pivot table on a new page called Worker List. This page can be hidden if you wish. A pivot table is the easiest way I can think of to make a unique list. I gave a named dynamic range to the list of names and use it to populate the list box.

    Here's more information about working with Excel Tables: http://www.utteraccess.com/wiki/inde...ables_in_Excel.

    As for the rest of the code, I added a check to see if the name in the row under consideration is on the list. If it isn't it skips the row.

    I have not done a lot of QA on this.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    09-10-2012
    Location
    Slovakia
    MS-Off Ver
    Excel 365
    Posts
    174

    Re: Create new sheets by using drop/down list

    Hi friend, thanks a lot. Tomorrow I will try and let you know status.

  6. #6
    Forum Contributor
    Join Date
    09-10-2012
    Location
    Slovakia
    MS-Off Ver
    Excel 365
    Posts
    174

    Re: Create new sheets by using drop/down list

    Dear, I already tried it and it partially works. I say partially because there is one problem with creation of new sheets. Refer to below summary.

    - list of workers with option to choose one or more works perfectly - OK.
    - copy/paste data for chosen worker/s to newly created sheet(s) works. - OK.
    - create new sheet only for chosen worker/s doesn't work as I need, because it creates sheets for all Account groups, not only for chosen worker/s. -WRONG.

    Example: If I choose Adrian, only one highlighted sheet has to be created according to account group, the rest couldn't (refer to enclosed picture).

    However let me correct mistake made by me in my original request.
    This macro will create (copy and rename sheet LOI 4.0) according to column "C" - Account Group.
    I forgot, that sheets are created per hidden text in column "A", not "C" as I originally wrote. Sorry for confusion.
    Attached Images Attached Images

  7. #7
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Create new sheets by using drop/down list

    OK, try moving
    Please Login or Register  to view this content.
    between these two lines so it looks like this
    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    09-10-2012
    Location
    Slovakia
    MS-Off Ver
    Excel 365
    Posts
    174

    Re: Create new sheets by using drop/down list

    Excellent. It works perfectly. It exactly reached my criteria. Thank you very much.

    Since this is something new for me, could you pls explain me (very simple) what exactly is Excel Binary Workbook? I see, that you changed format of my original example file from .xlsm to .xlsb. Does it related with using of the form? Or what is the main difference between .xlsm & .xlsb?

+ 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 New Sheets by Cycling Through Drop-Down List
    By cchase22 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-08-2015, 09:05 PM
  2. create dynamic drop down list for large list of data
    By Dariusd7 in forum Excel General
    Replies: 2
    Last Post: 05-10-2014, 04:39 AM
  3. 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
  4. 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
  5. How to create drop down list in Excel where each list item is based on two columns?
    By matkiros in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-11-2012, 08:50 AM
  6. 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
  7. Can I create a drop-down list that will reference other drop-down
    By fdebelo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-07-2006, 05:40 AM

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