Need VBA to automatically copy and paste entire row info to next sheet based on date

[MJ72]

I would like to develop a VBA that automatically copies entire row info from specific row on Sheet1 and pastes it into next available space in table on Sheet2 based on date info in column G (any row where date is older than 60 days/highlighted in red). Table in Sheet2 is virtually identical to Sheet1

 

[Joe4]

Here is another version of code that I think will work, regardless of whether the data is in a table or not:

Sub MyCopyMacro2()

    Dim lr1 As Long
    Dim r As Long
    Dim lr2 As Long
    Dim nr As Long
    
    Application.ScreenUpdating = False
    
'   Find last row with data on sheet 2
    lr2 = Sheets("Sheet2").Cells(Rows.Count, "G").End(xlUp).Row
    
'   Delete rows from sheet 2
    If lr2 > 1 Then Sheets("sheet2").Rows("2:" & lr2).Delete
    
'   Initialize first blank row number variables
    nr = 2
    
'   Find last row with data on sheet 1
    lr1 = Sheets("Sheet1").Cells(Rows.Count, "G").End(xlUp).Row
    
'   Loop through each row of data
    For r = 2 To lr1
'       See if date in column G is more than 60 days old
        If (Date - Sheets("Sheet1").Cells(r, "G")) > 60 Then
'           Copy to sheet2
            Sheets("Sheet1").Rows(r).Copy Sheets("Sheet2").Cells(nr, "A")
'           Increment new row counter
            nr = nr + 1
        End If
    Next r
    
    Application.ScreenUpdating = True

End Sub

 

[MJ72]

Here is another version of code that I think will work, regardless of whether the data is in a table or not:

Sub MyCopyMacro2()

    Dim lr1 As Long
    Dim r As Long
    Dim lr2 As Long
    Dim nr As Long
  
    Application.ScreenUpdating = False
  
'   Find last row with data on sheet 2
    lr2 = Sheets("Sheet2").Cells(Rows.Count, "G").End(xlUp).Row
  
'   Delete rows from sheet 2
    If lr2 > 1 Then Sheets("sheet2").Rows("2:" & lr2).Delete
  
'   Initialize first blank row number variables
    nr = 2
  
'   Find last row with data on sheet 1
    lr1 = Sheets("Sheet1").Cells(Rows.Count, "G").End(xlUp).Row
  
'   Loop through each row of data
    For r = 2 To lr1
'       See if date in column G is more than 60 days old
        If (Date - Sheets("Sheet1").Cells(r, "G")) > 60 Then
'           Copy to sheet2
            Sheets("Sheet1").Rows(r).Copy Sheets("Sheet2").Cells(nr, "A")
'           Increment new row counter
            nr = nr + 1
        End If
    Next r
  
    Application.ScreenUpdating = True

End Sub

Good Morning Joe,
Sorry for the delayed response, work has been insane. So I've tried both codes... first one deleted the first table but did not copy it over, the second just deleted the second table and gave a run time error. As for not being able to download the file, does it help if I told you that it was shared via Google Drive?.... and Sheet2 has now been formatted as a table. Here's a Dropbox link, maybe it'll be easier to open...

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[Joe4]

What is the name of the VBA module where you have placed this code?

 

[MJ72]

What is the name of the VBA module where you have placed this code?

MyCopyMacro - Sheet1

 

[Joe4]

MyCopyMacro - Sheet1

I think that is your issue right there. Since your code spans mutliple sheets, do not put it in the "Sheet1" module.
Insert your own module (and it will be named something like "Module1"), and then it will be generic and can be referenced by all sheets in your workbook.

 

[MJ72]

I think that is your issue right there. Since your code spans mutliple sheets, do not put it in the "Sheet1" module.
Insert your own module (and it will be named something like "Module1"), and then it will be generic and can be referenced by all sheets in your workbook.

Just tried that after you asked figuring that's where the conversation was headed and still no result, just a run time error.

 

[Joe4]

just a run time error.

What exactly does the run-time error say?
Does it give you a "Debug" option, and if it does, what line does it highlight?

When when you copied the code over to a general module, did you delete it from the Sheet module?

 

[MJ72]

What exactly does the run-time error say?
Does it give you a "Debug" option, and if it does, what line does it highlight?

When when you copied the code over to a general module, did you delete it from the Sheet module?

Yes, I deleted it from the sheet. Lol. Run-time error '1004' Delete method of Range class failed...Debug...line 5 "Range("A1").CurrentRegion.Delete"

 

[Joe4]

Yes, I deleted it from the sheet. Lol. Run-time error '1004' Delete method of Range class failed...Debug...line 5 "Range("A1").CurrentRegion.Delete"

That does not appear to be from either of the two latest versions of code that I posted, but rather from the first one, when I throught you had data in column A (which we later found out is not the case).

Try the newer versions of the code, not the original code.

 

[MJ72]

That does not appear to be from either of the two latest versions of code that I posted, but rather from the first one, when I throught you had data in column A (which we later found out is not the case).

Try the newer versions of the code, not the original code.

There will be data in the other columns.