+ Reply to Thread
Results 1 to 8 of 8

Help with auto sorting items onto different pages via selection

  1. #1
    Registered User
    Join Date
    05-30-2013
    Location
    Newark, Delaware
    MS-Off Ver
    Excel 2013
    Posts
    15

    Help with auto sorting items onto different pages via selection

    Not sure really how to explain this, but here is what I'm trying to accomplish. I'm working on a spreadsheet that will act as a live inventory tracker and accountability checklist. I want to be able to sort items via choices in drop down boxes and also to do a final list of all items that do not have a check mark next to them.

    So the main page would be a master list. The item should stay on this list at all times unless removed by the user. Depending on the drop box for the storage location would put the item on a new page that i would name via the storage areas. I was wondering if it could be done automatically but if not assigned to a macro button for sorting.

    Quick example of idea

    page 1 = master list page 2 = cage 1 page 3 = cage 2

    list of the items all listed on the master list, a column in the master page will have a drop down list with the item storage areas available. When the selected item is selected via drop down box it would then be sorted onto the correct page. All items would still stay on the master list.

    The master list would also have a column with a checkbox or yes /no depending on how it works and when a person is doing an inventory any item not found would be flagged and placed on another page.

    On the individual pages the items would be sorted from top to bottom, no empty spaces. if an item was moved from a storage area from cage 1 to cage 2 on the master list it would be removed from the wrong storage area and placed into the correct storage area when sorted.

    thanks for any help

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,033

    Re: Help with auto sorting items onto different pages via selection

    Hi William,

    I think you are looking for the Advanced Filter Tool built into Excel. If you start on the second sheet and create the filter and data on the first sheet, you can filter what you want to the second sheet.

    If you supply a sample file we can show you how.

    Here is a link: https://excelsemipro.com/2011/03/cop...vanced-filter/
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    05-30-2013
    Location
    Newark, Delaware
    MS-Off Ver
    Excel 2013
    Posts
    15

    Re: Help with auto sorting items onto different pages via selection

    I will look into this a little more but i checked out advanced filter and it works but not for what I'm looking to do. I'm looking to have a page already set up and then have a button or macro that will
    move the data to the new page based on the selection. Advanced filter seems to be for extracting information one time and not over and over again based on the changes to the master list. Also it cant be done
    from a button or automatically.

    When i get a second i will post the records i have so you can get a small idea. Thanks again

  4. #4
    Registered User
    Join Date
    05-30-2013
    Location
    Newark, Delaware
    MS-Off Ver
    Excel 2013
    Posts
    15

    Question Re: Help with auto sorting items onto different pages via selection

    Here is what i have come up with and it works fine but only for the one category, it does seem a little slow not sure if that is because I'm forcing it to look for 1000 rows even though i doubt the list will get that big but
    I'm not sure how to tell it to stop at the end of any entered data. I'm also confused on how to write more then one search item and target page in the same sub routine. I'm pretty sure what i came up with only works
    with one and that is it.

    My goal is to be able to sort up to 10 or more categories based on key text that will be found in the drop down box on column "K" then it will be copied starting at line 7 to the targeted worksheet. For now im trying to get this
    working but im hoping to make it that any changes made to the other worksheets will then show also on the master list. The data needs to stay on the master list and only be copied.

    Here is the simple one sort format i came up with.

    Sub SORT_MASTER_LIST()
    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet

    ' source set to master list, target set to same sheet as the column k keyword
    Set Source = ActiveWorkbook.Worksheets("MASTER LIST")
    Set Target = ActiveWorkbook.Worksheets("VAULT")

    j = 7 ' Start copying to row 7 in VAULT sheet
    For Each c In Source.Range("K7:K1007") ' Do 1000 rows, RANGE STARTS AT 7 no known way to stop at end of user data
    If c = "VAULT" Then
    Source.Rows(c.Row).Copy Target.Rows(j)
    j = j + 1
    End If
    Next c
    End Sub


    The above works but only for just the one keyword and one worksheet.

    looking to have any items with keywords like the following found in the master list copied /sorted out
    vault ---- copied to vault page (follows source format)
    motor pool---copied to motor pool page
    cage 1 ---- copied to cage 1
    etc -----

    the idea is that the code needs to be able to sort all the possible variable information to the correct page.

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,033

    Re: Help with auto sorting items onto different pages via selection

    Hi,

    Look at Dynamic Named Ranges to deal with not needing 1000 rows.
    Your code isn't using Advanced Filters, it is just doing a row by row copy. It would be nice to have a sample file to show you how to do this... I guess I'll make you one.

    Here is the sample file with lots of bells and whistles. You can have lots of different sheets or you may only need one with this scheme.

    AdvFilter OnChange Second Sheet.xlsm
    Last edited by MarvinP; 02-26-2019 at 12:56 AM.

  6. #6
    Registered User
    Join Date
    05-30-2013
    Location
    Newark, Delaware
    MS-Off Ver
    Excel 2013
    Posts
    15

    Re: Help with auto sorting items onto different pages via selection

    Sorry I'm actually answering or asking questions from my tablet and/or phone right now, I can try and post a copy of a general idea later just have to delete information from it. Thanks for any help. Slowly getting the hang of this stuff again

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,033

    Re: Help with auto sorting items onto different pages via selection

    I've edited my post above with a sample file. It has an Event Macro behind sheet2 which will redo the Advanced Filter when cell D2 is changed. See if this helps.

  8. #8
    Registered User
    Join Date
    05-30-2013
    Location
    Newark, Delaware
    MS-Off Ver
    Excel 2013
    Posts
    15

    Re: Help with auto sorting items onto different pages via selection

    So i copied the file and opened it and it is close to what I'm looking to do. It is grabbing all the information that is assigned to the cell d2 and pulling it correctly. I tried to modify it to work with the copy and
    paste to another sheet and well crashed excel in the progress. I will say that it looks like i could use this to create my drop down list and have it select files to be copied based on the name of that cell. The main difference
    is that your code is using one cell "D2" and mine would be using the dynamic range or cells on the master list. I think it is (k7-(whatever the end of the user imputed info is)) just think of having that selection box for
    "D2" all the way down the page for every row that a person has information entered. It would then take the resulting row and copy it to the correct page at the next available blank space.

    Below is a small sample, the drop down box is located in the storage column of the table. It has a list of drop down selections in excel that the user can select, upon selecting them the item should be moved
    to the correct worksheet page.
    value=vault in storage would have that item placed on the vault worksheet page at the next available blank space
    value= Truck E-4 in storage would copy that item to that E-4 worksheet page
    value=connex 1 in storage would copy that item to the Connex 1 worksheet page

    the pages are already made based on the options of the drop down selections. Then if possible is someone was looking at the E-4 worksheet they could select on the E-4 worksheet that the radio was moved back
    into the vault. From e-4 worksheet the item would be removed and relocated (or copied) to the vault worksheet and the master list page would show that item was also in the vault now under the storage column.

    hope this explains a little better. For now Im only concerned with getting things to the correct pages in one direction, doesn't look like excel is the choice for tracking a database



    DESCR SERIAL BARCODE STORAGE
    RADIO 12345 54321 VAULT
    RADIO 9999 8888 TRUCK E-4
    RADIO AB1234 67546 CONNEX 1

    Thanks again

+ 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. Replies: 1
    Last Post: 01-03-2017, 04:19 PM
  2. Adding Line Items to New Pages
    By Brogus in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-20-2016, 06:31 PM
  3. VBA - saving specific pages to PDF via Checkbox selection
    By fraser- in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-26-2016, 05:56 PM
  4. Printin a selection of pages
    By pinnerharrow in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 03-04-2012, 10:56 AM
  5. Multi select Listbox Items selection based on other Listbox item selection.
    By srinivassathi in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-20-2011, 05:53 AM
  6. VBA code to print 60+ items (60+ pages)
    By tn80 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-13-2010, 01:43 PM
  7. Pritning a selection of pages
    By Valery2105 in forum Excel General
    Replies: 1
    Last Post: 06-28-2006, 04:55 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