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]

Please try the last two codes that I posed, and not the first one.

 

[MJ72]

Please try the last two codes that I posed, and not the first one.

I've tried all three in both Sheets and in the module.

 

[MJ72]

I've tried all three in both Sheets and in the module.

Can you still not open the file?

 

[Joe4]

Can you still not open the file?

I see no option to download the file.
I do not use Google Sheets, so I am not familiar with it.
Can you tell me how to download the file from the link you provided?

And just to confirm:
You are really doing this in Excel, and not in Google Sheets, right?

 

[MJ72]

I see no option to download the file.
I do not use Google Sheets, so I am not familiar with it.
Can you tell me how to download the file from the link you provided?

And just to confirm:
You are really doing this in Excel, and not in Google Sheets, right?

I provided another link to dropbox and yes, I am indeed using Microsoft Excel. ?

 

[Joe4]

I was able to download your second attempt, and I see two things causing issues:
1. Your data on each sheet starts on row 3, not row 2
2. You have put the code in the "Sheet1" module. Do NOT put it there! Those modules are for event procedure (automated event code) that affects that sheet. We are trying to interact between two sheets. You are going to want to insert a new general module in this workbook (it will automatically be named something like "Module1") and place the code in here so it is accessible from all sheets.

Here is what the revised code should look like (I ran it on your sample sheet and it worked!).

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 > 2 Then Sheets("sheet2").Rows("3:" & lr2).Delete
    
'   Initialize first blank row number variables
    nr = 3
    
'   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 = 3 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]

I was able to download your second attempt, and I see two things causing issues:
1. Your data on each sheet starts on row 3, not row 2
2. You have put the code in the "Sheet1" module. Do NOT put it there! Those modules are for event procedure (automated event code) that affects that sheet. We are trying to interact between two sheets. You are going to want to insert a new general module in this workbook (it will automatically be named something like "Module1") and place the code in here so it is accessible from all sheets.

Here is what the revised code should look like (I ran it on your sample sheet and it worked!).

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 > 2 Then Sheets("sheet2").Rows("3:" & lr2).Delete
   
'   Initialize first blank row number variables
    nr = 3
   
'   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 = 3 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

Thank you again for your help, Joe, and your patience to explain why and where the code should go. If I could ask one more favour to help with my VBA education...could you tell me what each of these steps are doing? Obviously I can figure out the "copy" and "delete" etc. steps... but things like the "Dim lr1 As Long" and setting things as false etc... I'm still learning the language so I'd much appreciate the tutelage.

 

[Joe4]

Thank you again for your help, Joe, and your patience to explain why and where the code should go. If I could ask one more favour to help with my VBA education...could you tell me what each of these steps are doing? Obviously I can figure out the "copy" and "delete" etc. steps... but things like the "Dim lr1 As Long" and setting things as false etc... I'm still learning the language so I'd much appreciate the tutelage.

I documented a lot of the code already, and I can explain a little more, but unfortunately, I really cannot condense a whole course on VBA in a few short posts here (and that is not what this site is really intended for). In that case, I would highly recommend picking up an introductory VBA code, like this one here: Microsoft Excel 2019 VBA and Macros

The "Dim" stated are declaring your variables before using them. This helps, especially with debugging your code, in two main ways:
1. Restrict the data that you can put in the variables. So, if you tried to put a text value in a variable that was set up to be an Integer, you would get an error.
2. When used with "Option Explicit", it can help detect typos in your variables.
See here: VBA Option Explicit | How to Use Option Explicit Statement in Excel VBA?

What the following line does:

Application.ScreenUpdating = False

is temporarily suspend all screen updates made by the result of your code running. You typically see this near the beginning of the code.

And then you typically see a line like this near the end of the code:

Application.ScreenUpdating = True

this turns screen updating back on.

So by using these two lines like this, is suspend all screen updates until the very end of the code, and do them all at once. By waiting until the end of the code to do this, and to do it all at once, it speeds up your code,

I think the rest of the code is documented, so hopefully those things are obvious.
If you have any other specific questions, please let me know.

 

[MJ72]

I documented a lot of the code already, and I can explain a little more, but unfortunately, I really cannot condense a whole course on VBA in a few short posts here (and that is not what this site is really intended for). In that case, I would highly recommend picking up an introductory VBA code, like this one here: Microsoft Excel 2019 VBA and Macros

The "Dim" stated are declaring your variables before using them. This helps, especially with debugging your code, in two main ways:
1. Restrict the data that you can put in the variables. So, if you tried to put a text value in a variable that was set up to be an Integer, you would get an error.
2. When used with "Option Explicit", it can help detect typos in your variables.
See here: VBA Option Explicit | How to Use Option Explicit Statement in Excel VBA?

What the following line does:

Application.ScreenUpdating = False

is temporarily suspend all screen updates made by the result of your code running. You typically see this near the beginning of the code.

And then you typically see a line like this near the end of the code:

Application.ScreenUpdating = True

this turns screen updating back on.

So by using these two lines like this, is suspend all screen updates until the very end of the code, and do them all at once. By waiting until the end of the code to do this, and to do it all at once, it speeds up your code,

I think the rest of the code is documented, so hopefully those things are obvious.
If you have any other specific questions, please let me know.

Thank you very much. Your help has been awesome. Cheers!

 

[Joe4]

You are welcome.

I re-marked the solution. You want to mark the solution that contains the answer to the original question (i.e. the VBA code that works), not the post acknowledging that there is a solution (you typically would not mark one of your own posts as the solution, unless you can up with the answer yourself and posted that answer).