How do I automatically move a row when the date in a cell arrives?

[mikemgregory]

I have a multiple sheet Excel file...... one sheet is labeled "current" and the other is labeled "future". How can I automatically move a row of data from the "future" sheet to the "current" sheet when the date on a specific row arrives?

 

[kevin9999]

Possibly a Workbook_Open() event code? If you could provide copies of the 2 sheets involved using the XL2BB add in, or alternatively, share your file via Google Drive, Dropbox or similar file sharing platform, then I'm sure you will get a workable solution from the forum.

 

[mikemgregory]

Possibly a Workbook_Open() event code? If you could provide copies of the 2 sheets involved using the XL2BB add in, or alternatively, share your file via Google Drive, Dropbox or similar file sharing platform, then I'm sure you will get a workable solution from the forum.

I have a multiple sheet Excel file...... one sheet is labeled "current" and the other is labeled "future". How can I automatically move a row of data from the "future" sheet to the "current" sheet when the date on a specific row arrives?

Here is the file I'm working with Order Sheet MRE.xlsm

 

[kevin9999]

Please try the following on a copy of your workbook. Put the code in the "ThisWorkbook" module of your file (double click ThisWorkbook in the VB editor). It should move any records from the FUTURE sheet to the CURRENT sheet when the date in column L is today's date (or earlier) whenever the file is opened.

Private Sub Workbook_Open()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("FUTURE")
    Set ws2 = Worksheets("CURRENT")
    
    If ws1.AutoFilterMode Then ws1.AutoFilter.ShowAllData
    With ws1.Range("A1").CurrentRegion
        .AutoFilter 12, "<=" & Format(Date, "m/d/yyyy")
        If WorksheetFunction.Subtotal(3, .Columns(12)) > 1 Then
            .Offset(1).Resize(.Rows.Count - 1).Copy
            With ws2.ListObjects(1).Range
                .Cells(.Rows.Count + 1, 1).PasteSpecial xlValues
            End With
        End If
            .Offset(1).EntireRow.ClearContents
        ws1.AutoFilter.ShowAllData
    End With
    
    With ws2.ListObjects(1).Sort
        .SortFields.Clear
        .SortFields.Add _
          Key:=ws2.ListObjects(1).ListColumns(12).Range, _
          Order:=xlAscending
        .Header = xlYes
        .Apply
    End With 
End Sub

Link to the demonstration file.

 

[mikemgregory]

Please try the following on a copy of your workbook. Put the code in the "ThisWorkbook" module of your file (double click ThisWorkbook in the VB editor). It should move any records from the FUTURE sheet to the CURRENT sheet when the date in column L is today's date (or earlier) whenever the file is opened.

Private Sub Workbook_Open()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("FUTURE")
    Set ws2 = Worksheets("CURRENT")
   
    If ws1.AutoFilterMode Then ws1.AutoFilter.ShowAllData
    With ws1.Range("A1").CurrentRegion
        .AutoFilter 12, "<=" & Format(Date, "m/d/yyyy")
        If WorksheetFunction.Subtotal(3, .Columns(12)) > 1 Then
            .Offset(1).Resize(.Rows.Count - 1).Copy
            With ws2.ListObjects(1).Range
                .Cells(.Rows.Count + 1, 1).PasteSpecial xlValues
            End With
        End If
            .Offset(1).EntireRow.ClearContents
        ws1.AutoFilter.ShowAllData
    End With
   
    With ws2.ListObjects(1).Sort
        .SortFields.Clear
        .SortFields.Add _
          Key:=ws2.ListObjects(1).ListColumns(12).Range, _
          Order:=xlAscending
        .Header = xlYes
        .Apply
    End With
End Sub

Link to the demonstration file.

Thank you Kevin......

 

[kevin9999]

Happy to help, and thanks for the feedback