Results 1 to 5 of 5

Event macro to move rows based on Column

Threaded View

  1. #1
    Registered User
    Join Date
    06-10-2013
    Location
    boston
    MS-Off Ver
    Excel 2010
    Posts
    79

    Event macro to move rows based on Column

    Hey everyone,

    I have a tracking workbook that tracks open items, when an item becomes closed, the user selects the "Status" Column and chooses closed. When closed is selected, a window pops up with confirmation of the selection, and then it inserts the date in the next column and moves the row to the Closed tab in the workbook and deletes the row from Open tab of the workbook. This all works great. Now I need the code to have another which will essentially do the same thing. The user can either choose either to Close the item or monitor the item, then it will be moved to either the "Closed" tab or the "Monitoring" tab. I used the exact same event code and changed a few things but when "Monitor" is selected, nothing happens. The code is below and an example workbook is attached with the code. The first event works, the second event does nothing. Any help would be great.

    Private Sub Worksheet_Change(ByVal Target As Range)
    
     Application.EnableEvents = False
    
    
      If Target.Column = 15 Then
       
       
       If Target = "Closed" Then
        Dim nResult As Long
        nResult = MsgBox( _
        Prompt:="Is this Item Closed?", _
        Buttons:=vbYesNo)
        If nResult = vbNo Then
        Exit Sub
        End If
        
        Target.Offset(0, 1).Value = Now
      
        nxtRw = Sheets("Closed").Range("A" & Rows.Count).End(xlUp).Row + 1
                 
          Target.EntireRow.Copy Destination:=Sheets("Closed").Range("A" & nxtRw)
    
          Target.EntireRow.Delete shift:=xlUp
          
       End If
      End If
    
    Application.EnableEvents = True
    
    End Sub
    
    
    Private Sub Worksheet_Change1(ByVal Target As Range)
    
    
     Application.EnableEvents = False
    
    
      If Target.Column = 15 Then
       
       
       If Target = "Monitor" Then
        Dim nResult1 As Long
        nResult1 = MsgBox( _
        Prompt:="Monitor this Item?", _
        Buttons:=vbYesNo)
        If nResult1 = vbNo Then
        Exit Sub
        End If
        
        Target.Offset(0, 1).Value = Now
      
        nxtRw = Sheets("Monitoring").Range("A" & Rows.Count).End(xlUp).Row + 1
                 
          Target.EntireRow.Copy Destination:=Sheets("Monitoring").Range("A" & nxtRw)
    
          Target.EntireRow.Delete shift:=xlUp
          
       End If
      End If
    
     Application.EnableEvents = True
     
    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Move data in rows to columns based on unique ID in column A
    By ssdsibes in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-04-2013, 07:04 AM
  2. HELP: Move values from column to rows based on Column A
    By ExceJunkie in forum Excel General
    Replies: 2
    Last Post: 05-30-2013, 08:09 AM
  3. Replies: 1
    Last Post: 03-17-2013, 06:52 AM
  4. How do I create a Macro to move rows based on a specific value.
    By Total Excel Noob in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-30-2012, 03:11 PM
  5. Move rows to another sheet based on column input
    By lacerz in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-01-2010, 01:02 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