Copy from active sheet to on-going list in another sheet

[y3tter]

Could anyone help me with VBA for copying data from active sheet and pasting in the first available row in sheet titled Archive. I'm trying to copy Range A2:G2, paste in the first available row in Archive sheet. Then in the active sheet delete cells D2, F2 and G2.

 

[Joe4]

Try this:

Sub MyCopy()

    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim nr As Long
    
'   Capture sheets
    Set ws1 = ActiveSheet
    Set ws2 = Sheets("Archive")
    
'   Find next available row on Archive sheet (by looking at entries in column A)
    nr = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1
    
'   Copy data from cells A2:G2 on activesheet to next available row in archive sheet
    ws1.Range("A2:G2").Copy ws2.Range("A" & nr)
    
'   Clear values from D2:G2 on activesheet
    ws1.Range("D2:G2").ClearContents
        
End Sub

 

[y3tter]

Try this:

Sub MyCopy()

    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim nr As Long
    
'   Capture sheets
    Set ws1 = ActiveSheet
    Set ws2 = Sheets("Archive")
    
'   Find next available row on Archive sheet (by looking at entries in column A)
    nr = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1
    
'   Copy data from cells A2:G2 on activesheet to next available row in archive sheet
    ws1.Range("A2:G2").Copy ws2.Range("A" & nr)
    
'   Clear values from D2:G2 on activesheet
    ws1.Range("D2:G2").ClearContents
        
End Sub

Works great, one thing though. The first two cells contain a formula, so I get a #REF ! error. Is there a way to get it to paste value?

 

[Joe4]

Try this variation:

Sub MyCopy()

    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim nr As Long
    
'   Capture sheets
    Set ws1 = ActiveSheet
    Set ws2 = Sheets("Archive")
    
'   Find next available row on Archive sheet (by looking at entries in column A)
    nr = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1
    
'   Copy data from cells A2:G2 on activesheet to next available row in archive sheet
    ws1.Range("A2:G2").Copy
    ws2.Range("A" & nr).PasteSpecial xlPasteValues
    
'   Clear values from D2:G2 on activesheet
    ws1.Range("D2:G2").ClearContents
        
End Sub

 

[y3tter]

Try this variation:

Sub MyCopy()

    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim nr As Long
    
'   Capture sheets
    Set ws1 = ActiveSheet
    Set ws2 = Sheets("Archive")
    
'   Find next available row on Archive sheet (by looking at entries in column A)
    nr = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1
    
'   Copy data from cells A2:G2 on activesheet to next available row in archive sheet
    ws1.Range("A2:G2").Copy
    ws2.Range("A" & nr).PasteSpecial xlPasteValues
    
'   Clear values from D2:G2 on activesheet
    ws1.Range("D2:G2").ClearContents
        
End Sub

PERFECT!!! Thank you so MUCH!

 

[Joe4]

You are welcome.