+ Reply to Thread
Results 1 to 8 of 8

VBA Change Event, Move multiple rows based on drop down selection for multiple criteria

  1. #1
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    VBA Change Event, Move multiple rows based on drop down selection for multiple criteria

    I had a project management file before that I used a changed event macro, but it was all based on each project being in 1 row. And of course this would mean that within one column there would be an abundance of data as comments were added. it made it difficyult to manage multiple projects trying to scroll through them. if is not uncommon for me to manage 12+ projects, and I wanted an more efficient way to do it. I did some searching, and found this template online that I liked, and then tweeked it to my needs. Now I could really use your help to kick it up a notch. As expected projects will be completed. I don't want to delete them, only to move the project rows (7 rows per project) to the "Completed_Projects" tab.

    Headers reside on row 1 (on both tabs)
    Each Project is a total of 7 rows (2-8, 9-15, 16-22, etc)
    I want the trigger (if possible) to be if ALL 4 of the items for each project (within those 7 rows) to show as Complete
    ... For example if K3-K6 are All listed as Completed, then rows 2-8 are moved to Projects_Completed tab.
    ... If it is not possible to set this based on mustiple cells, I would choose, the 4th dropdown, which is K6, then rows 2-8 could be moved.
    Obviously each time a project is moved to the completed_Projects tab it is placed on the next available blank row.

    The code I used on my original project which works flawlessly is shown below. I imagine making modifications to the code below would be easier than attempting to start from scratch.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Webbers; 01-31-2017 at 02:52 PM.
    ~*~ Sherry ~*~
    Poinciana, FL

  2. #2
    Valued Forum Contributor
    Join Date
    08-07-2014
    Location
    Quito, Ecuador
    MS-Off Ver
    Excel 2016 & 365, Windows 10
    Posts
    511

    Re: VBA Change Event, Move multiple rows based on drop down selection for multiple criteri

    Hi @ Webbers

    About the trigger:
    For example if J3-J6 are All listed as Completed, then rows 2-8 are moved to Projects_Completed tab.
    Perhaps you mean K3-K6 (yellow highlighted) marked as Complete ?
    If all 4 cells in column K for each project are marked as Complete...then it means the project is complete...no need to check the status in columns C,E,G,I ?
    Barriers are there for those who don't want to dream

  3. #3
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    Re: VBA Change Event, Move multiple rows based on drop down selection for multiple criteri

    yes Vichopalcios, you are correct, I DID mean to say K3-K6 (yellow highlighted) rather than J3-J6. All Previous columns would NOT need to be verified if all 4 of these cells is listed as Complete. I have updated my post to reflect K3-K6 rather than J3-J6, sorry about the confusion.

  4. #4
    Valued Forum Contributor
    Join Date
    08-07-2014
    Location
    Quito, Ecuador
    MS-Off Ver
    Excel 2016 & 365, Windows 10
    Posts
    511

    Re: VBA Change Event, Move multiple rows based on drop down selection for multiple criteri

    try this:

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    Re: VBA Change Event, Move multiple rows based on drop down selection for multiple criteri

    Works great Vichopalcios. I do have one small question. On the Completed_Projects tab, my "matching" headers are already listed in row 1. When I marked a project as complete, it was moved to the Completed_Projects tab as it should, but the first completed project appeared on row 4, leaving 2 blank rows between the header and the first completed project. I then marked the second project as complete and it sghows direcly under the 1st one, so that all works perfectly. I looked at the code and I am not sure where this portion could be... since the first completed project is done once, it is certainly easy enough to delete rows 2 & 3. I guess I am trying to understand why more than anything.

  6. #6
    Valued Forum Contributor
    Join Date
    08-07-2014
    Location
    Quito, Ecuador
    MS-Off Ver
    Excel 2016 & 365, Windows 10
    Posts
    511

    Re: VBA Change Event, Move multiple rows based on drop down selection for multiple criteri

    The problem arises from your formating. !!
    When you select, copy, move, delete ranges, and inside those ranges you have merged cells, like you have in your Project sheet, by sure, you are going to face some problems.
    My advise is to avoid merging cells.( If you really need it, you can use Cell Format.....Align....Center in selection.!!)
    Perhaps you noticed that I changed from A to B, and from 1 to 3 in
    Please Login or Register  to view this content.
    Just to test my point, change this line to "+2" instead of "+3" and run the macro.... you will expect to copy the range just one row before, but...disaster!!! it overwrites the previous project.
    So I prefered to leave 2 blank rows at the beginning, and the rest will be OK.

  7. #7
    Valued Forum Contributor
    Join Date
    08-07-2014
    Location
    Quito, Ecuador
    MS-Off Ver
    Excel 2016 & 365, Windows 10
    Posts
    511

    Re: VBA Change Event, Move multiple rows based on drop down selection for multiple criteri

    Hey @ Webbers

    To solve that problem you just need to add one single line to the code, so the first time, when LR = 4 change it to LR=2

    Please Login or Register  to view this content.
    I hope, this solve your thread, and thanks for the reps.

    Vicho

  8. #8
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    Re: VBA Change Event, Move multiple rows based on drop down selection for multiple criteri

    Vicho, thanks so much! Yes, I thought of that, and tried it, and when I saw the overwrite... I was like Major oops here!!! I went back to my previous version with your original code.
    Last edited by Webbers; 02-02-2017 at 11:54 AM.

+ 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. Select a cell at random based on multiple selection criteria across multiple sheets.
    By scottyms in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-03-2016, 09:44 AM
  2. Change PivotTable selection to multiple criteria based on table value
    By RaydenUK in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-24-2015, 08:44 AM
  3. [SOLVED] create a drop down which allows multiple selection and change content of table based on th
    By melody10 in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 08-16-2015, 06:12 AM
  4. Replies: 1
    Last Post: 06-25-2015, 07:29 AM
  5. [SOLVED] using SUMIFS and adding values based on multiple criteria based on selection from the drop
    By muheebrahman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-25-2015, 12:55 AM
  6. [SOLVED] Worksheet Change Event - Multiple Cell Selection
    By mojo249 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-02-2012, 08:30 PM
  7. Replies: 1
    Last Post: 04-05-2012, 07:48 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