Macro that moves an entire row from one sheet to another based on date in one cell and value in other cell.

[daoteez]

I use a spreadsheet to track when I need to call a client after a set period within a given territory. Each client territory is represented on a different sheet within my workbook. I already have a macro that can move a client, represented by a row of data, between any of the sheets of the workbook based on a drop-down selection within that row. After I have met with a client I will move them to an “on hold” sheet in the workbook.

On this “on hold” sheet I have an expiration date next to each client data row of when I need to contact each respective client next. What I’m looking for is a macro that will recognize that expiration date on a given client row of data and automatically move that client data back to its respective territory sheet based on a respective cell within that client row once that expiration date has been met. The reference cell would have the name of the territory the client belongs to. E.g. Los Angeles or Pasadena.

 

[Legacy 143009]

Glad it did help

 

[daoteez]

Glad it did help

Hey there! Code is still working great but I wanted to see if you might help me alter it for one other sheet that is in the same workbook. The other sheet ("Meeting") would have a date in the next column on the right of the original date column from this original function. The function would be similar, except when the date is reached it would move the row to the "On Hold" sheet instead of it's original location. Do you think this is something that might be accomplished?

 

[Legacy 143009]

On which column is that date?

 

[daoteez]

On which column is that date?

It should be column “S” as I believe the original code had the other date in column “R”. I forgot to add, if possible when I input the date in column “S” is there a way to have the date in column “R” populate with whatever the date is 60 days later?

 

[Legacy 143009]

It should be column “S” as I believe the original code had the other date in column “R”. I forgot to add, if possible when I input the date in column “S” is there a way to have the date in column “R” populate with whatever the date is 60 days later?

That's easy. Do you want to compare column S or R with Now?

So, "If column R (60 days later) is today, then, move to On Hold list". Something like this?

 

[daoteez]

That's easy. Do you want to compare column S or R with Now?

So, "If column R (60 days later) is today, then, move to On Hold list". Something like this

I would want to compare column "S" to "Now" i.e. whatever the current date is. So for example column "S" is my meeting date column and if I have a meeting on 02/15/23 then Column "R" would be 60 days from 02/15/23. Once "Now" is past the date in column "S" it would trigger the move of the row data to the "On Hold" tab. The date data in column R would still remain in tact and then once "Now" is past the date in column R it would trigger the original macro created.

One minor change, I had to delete a column of data in my work book which was to the left of the original date columns so column "R" has become column "Q". I already updated the original macro to reflect this change but this new macro should actually be columns Q (date + 60) and R (meeting date) instead of columns R and S.

 

[Legacy 143009]

Ahh OK.. So you want when it enters to 60-day period. Ok no problem.

Paste this code inside the "Meeting" sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Columns("S")) Is Nothing Then
    Application.EnableEvents = False
      Target.Offset(, -1).Value = Target.Value + 60
    Application.EnableEvents = True
  End If
End Sub

Paste this right after our first code inside the workbook open event:

  With Worksheets("Meeting")
  lRow = .Cells(Rows.Count, 1).End(xlUp).Row
  For i = lRow To 2 Step -1
    If .Cells(i, 19).Value <> "" And .Cells(i, 19).Value < Now Then
      Rows(i).Cut
      Worksheets("On Hold").Cells(Rows.Count, 1).End(xlUp).Offset(1).Insert
    End If
  Next
  End With

 

[daoteez]

Ahh OK.. So you want when it enters to 60-day period. Ok no problem.

Paste this code inside the "Meeting" sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Columns("S")) Is Nothing Then
    Application.EnableEvents = False
      Target.Offset(, -1).Value = Target.Value + 60
    Application.EnableEvents = True
  End If
End Sub

Paste this right after our first code inside the workbook open event:

  With Worksheets("Meeting")
  lRow = .Cells(Rows.Count, 1).End(xlUp).Row
  For i = lRow To 2 Step -1
    If .Cells(i, 19).Value <> "" And .Cells(i, 19).Value < Now Then
      Rows(i).Cut
      Worksheets("On Hold").Cells(Rows.Count, 1).End(xlUp).Offset(1).Insert
    End If
  Next
  End With

Just reading the code, since we’re using columns “Q” and “R” instead now I think it needs to update to column “R” in the macro for the meeting tab no? And the other macro I think the number goes to 18 too I believe.

 

[Legacy 143009]

Yes, It shouldn't be a problem. Let me know if you have unexpected result.

 

[daoteez]

Yes, It shouldn't be a problem. Let me know if you have unexpected result.

When I input a date on column "R" on the meeting tab a VBA error box came up. It says "Compile error: Ambiguous name detected: Worksheet_Change"