Results 1 to 8 of 8

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

Threaded View

  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.

    Option Explicit
    Dim Flag As Boolean
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim LR As Long
        LR = Range("A" & Rows.Count).End(xlUp).Row
        If Flag = True Then Exit Sub
    
    '' Column K is Status Column
    '' Column K is where Designated "Target" appears
        If Not Intersect(Target, Range("K2:K" & LR)) Is Nothing Then
    
    '' Complete is the "trigger status used
            If Target.Text = "Complete" Then
                LR = Sheets("Completed_Projects").Range("A" & Rows.Count).End(xlUp).Row + 1
                Target.EntireRow.Copy
                Sheets("Projects_Complete").Range("A" & LR).PasteSpecial
                Flag = True
                Target.EntireRow.Delete
            End If
        End If
        Application.CutCopyMode = False
        Flag = False
    End Sub
    Attached Files Attached Files
    Last edited by Webbers; 01-31-2017 at 02:52 PM.
    ~*~ Sherry ~*~
    Poinciana, FL

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