• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Is VBA that can do this possible?

cmkarnes

Member
I've never seen anyone ask this before - I've included code for a large Excel table I have. For the first statement of the vba, I've got it set so when a date is entered in a particular cell, the entire row automatically rolls over to a different tab in the workbook. The code just picks up on if the cell is empty or not.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
            Dim LastRow  As Long
            Dim WkRg As Range
                If (Target.Column = 27) Then
              If (Target.Value = "<>""") Then Exit Sub
              Set WkRg = Range(Cells(Target.Row, "A"), Cells(Target.Row, "AH"))
                  Application.EnableEvents = False
              With Sheets("COMPLETED")
                    LastRow = .Range("A" & Rows.Count).End(xlUp).Row
                    WkRg.Copy Destination:=.Cells(LastRow + 1, 1)
                    WkRg.EntireRow.Delete
              End With
              Application.EnableEvents = True
              Exit Sub
            Else
              If (Target.Column <> 28) Then Exit Sub
              If (Target.Value = "<>""") Then Exit Sub
   
              Set WkRg = Range(Cells(Target.Row, "A"), Cells(Target.Row, "AH"))
             
              Application.EnableEvents = False
              With Sheets("INACTIVE")
                    LastRow = .Range("A" & Rows.Count).End(xlUp).Row
                    WkRg.Copy Destination:=.Cells(LastRow + 1, 1)
                    WkRg.EntireRow.Delete
              End With
              Application.EnableEvents = True
             
            End If
End Sub
All works perfect.

Now, what they want is to have the row move over to the new tab one day after the date that is entered in the cell. So for example, if the date entered is 03/01/16, they don't want the row to move over until 03/02/16.

I did not know if possible with vba? Thanks!
 
You'd need a new routine that ran on, say, Workbook_Open, that checks columns 27 and 28 for all rows and moves any with yesterday's date.
 
I need assistance with writing the vba though. I do so so with it, but I need help constructing the statement. Can someone assist? Thanks
 
The date statement would apply to the first portion to the attached vba that references the "Completed" table - that is the table where the data rolls over to. The sheet where the original info gets entered is the "Active" tab (it's a table). Thanks!
 
Back
Top