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]

See my sample setup below. The code will move client if expr. date has past today's date.

Code:

Sub moveClient()
  Dim lRowOnHold As Long, lRowTerrSheet As Long
  With Worksheets("On Hold")
  lRowOnHold = .Cells(Rows.Count, 1).End(xlUp).Row
  For i = 2 To lRowOnHold
    If .Cells(i, 2).Value < Now Then
      For Each ws In Worksheets
        If ws.Name = .Cells(i, 3).Value Then
          lRowTerrSheet = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
          ws.Cells(lRowTerrSheet, 1).Value = .Cells(i, 1).Value
          Rows(i).EntireRow.Delete
          Exit For
        End If
      Next
    End If
  Next
  End With
End Sub

After code run:

 

[daoteez]

Wow, this is awesome and it fascinates me how easy it is for someone with experience to create this! I have one layer of complexity that I didn’t think of until now that might complicate this. The other macro I use to move these clients to different sheets relies on an option selected from a drop-down cell within the row. So when I move the client to “on hold” tab. The drop down cell also reflects “on hold” as the option. My concern is that once this new macro moves the row to the original sheet that old “on hold” option will still be reflected within the cell on that respective row which could potentially trigger the row to move back to the “on hold” sheet.

Is there a way to reset this drop down cell to reflect the appropriate territory once the other macro moves it back? I realize I’m probably going to have to share the macro I’m using for this to make sense which I’m happy to do tomorrow once I’m in my office.

The other option I’m thinking is that perhaps there is a way to just leave the drop down cell behind on the “on hold” sheet. E.g. row a data would transfer columns 1,2,4,5, and 6 and then paste over to the same corresponding columns in the original territory tab leaving column 3 the “on hold” drop down behind. Then I would just copy and paste a drop down box from one of the other rows and problem solved.

Is either option viable?

 

[Legacy 143009]

I have to see your sample data. Let's say, you have the dropdown box on column D when you move the data to "On Hold" list. Then this code will move the client with dropdown column and set the status to active:

Sub moveClient()
  Dim lRow As Long
  With Worksheets("On Hold")
  lRow = .Cells(Rows.Count, 1).End(xlUp).Row 'Get the last row in "On Hold" sheet
  For i = 2 To lRow 'Loop until last row
    If .Cells(i, 2).Value < Now Then 'If column B (Exp. Date) value older than today
      For Each ws In Worksheets ' For each worksheet
        If ws.Name = .Cells(i, 3).Value Then 'If sheet name equals to column C (Region) value
          .Cells(i, 4).Value = "Active" 'Set dropdown value in column D to "Active"
          Rows(i).Cut 'Cut the entire row
          ws.Cells(Rows.Count, 1).End(xlUp).Offset(1).Insert 'Insert to end of Region sheet
          Exit For 'Exit looping sheets and move to the next record in "On Hold" sheet
        End If
      Next
    End If
  Next
  End With
End Sub

 

[daoteez]

Here is the Macro I currently have on my workbook.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  7/6/2022  7:36:49 AM  EDT Original code by My Aswer Is This
'Modified  7/6/2022  14:24:17 AM  EDT Updated by Jimmypop - Added funtionality so sheet is created automatically if not exists
    If Target.Column = 3 Then
        Dim r As Long
        Dim ans As String
        Dim newSheetName As String
        Dim checkSheetName As String
        Dim Lastrow As Long
        r = Target.Row
        ans = Target.Value
        newSheetName = ans
        On Error Resume Next
        checkSheetName = Worksheets(newSheetName).Name
        If checkSheetName = "" Then
            Worksheets.Add.Name = newSheetName
            MsgBox "The sheet named ''" & newSheetName & _
            "'' does not exist in this workbook but it has been created now.", _
            vbInformation, "System Message"
        Else
''Do nothing
        End If
        Lastrow = Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1
        Rows(r).Copy Sheets(ans).Rows(Lastrow)
        Rows(r).Delete
    End If
    Exit Sub
End Sub

It provides two functions:
1. it will transport the client row data to which ever options is selected in the drop down by within that row. There are currently 16 different options in the drop down I can select.
2. If the respective sheet for that option does not exist then the macro will create a new sheet and transport that respective row to the newly created sheet. e.g. if I happen to add "Glendale" to drop down selection but I haven't actually created a "Glendale" sheet, a new sheet will be created with "Glendale" and the row will be transported there.
Based on this second Macro you created I'm not certain it will work as desired with the existing Macro. Is it possible to try using a Macro try option 2?

The other option I’m thinking is that perhaps there is a way to just leave the drop down cell behind on the “on hold” sheet. E.g. row a data would transfer columns 1,2,4,5, and 6 and then paste over to the same corresponding columns in the original territory tab leaving column 3 the “on hold” drop down behind. Then I would just copy and paste a drop down box from one of the other rows and problem solved.

 

[Legacy 143009]

It would be much better if you can provide some sample model.

 

[daoteez]

It would be much better if you can provide some sample model.

Ok I took a few images of what the database looks like sans client data. The "On Hold" sheet using your new macro would reference column G for the original sheet territory location of the client row and column R for the expiration date. The complication I have is column c is the drop down box that utilizes the existing macro I have to transport these respective rows to any sheet within my workbook. e.g. If I selected the drop down "On Hold" and changes it to "1-Century City", the macro would move that respective row over to the "1-Century City" sheet.

What I'm trying to accomplish is for that column c drop down to update to the correct drop down selection once the new macro moves the row at expiration time e.g. in my previous example the drop down would automatically update from "on hold" to "1-Century City" once the new macro moves that row back to it's respective territory sheet. Or the other option is to have the new macro disregard and not copy/delete the data in column c and only move the data in the rest of the row to it's respective sheet. Is either option possible?

 

[Legacy 143009]

Ok thank you. I am not in front of the computer now. I will check tomorrow

 

[daoteez]

Ok thank you. I am not in front of the computer now. I will check tomorrow

I was thinking about a solution that could potentially work? Is it possible in the macro you created for the drop down cell to reference a specific cell as it’s new value instead of “Active”? If we’re using the cell in column G to reference the new location for the row of data could the macro then copy a specific cell in that new sheet location for the updated value in the drop down cell?

 

[Legacy 143009]

We can set the dropdown value directly to sheet name.