trying to amend this code to auto save new data

[steve400243]

Hello, I am using this code to transfer data from one sheet to another. I need to be able to add new data to the Complete tab on the next line down, and keep the existing data. currently it clears the old data when new data from the call in log is transferred. Thanks for any help provided.

Sub cpyToComplete() 
     
     
    Sheets("Complete").Range("A2:I").Value = Sheets("FXF CALL IN LOG").Range("A3:I").Value 
    Sheets("Complete").Range("J1").Value = Sheets("FXF CALL IN LOG").Range("I1").Value 
     
    Sheets("FXF CALL IN LOG").Range("A3:I").Value = "" 
     
     
End Sub

[vcoolio]

Hello Steve,

It appears that you are transferring a complete, hard-coded data block to the second sheet (always to the same area: "A2:I") which is why the older data is being overwritten. You may have to try a different tack, such as:-

Sub CopyData()

    Dim lrow As Long

Application.ScreenUpdating = False

lrow = Range("A" & Rows.Count).End(xlUp).Row

If lrow > 2 Then
Sheet1.Range("A3:I" & lrow).Copy
Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
Sheet1.Range("A3:I" & lrow).ClearContents
End If

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

where sheet1 (Call in Log) is the source sheet and sheet2 is the destination sheet (Complete).

This will add the transferred data to the bottom of the existing data in sheet2.

(You could do the same for the second line of code in your opening post and add it in to the above code).

I hope that this works for you.

Cheerio,
vcoolio.

[steve400243]

thanks Vcoolio, appreciate your time looking at it. I will test this one in a little bit. I got it working with this code -

Sub cpyToComplete()
     
Application.ScreenUpdating = False
Dim CopySheet As Worksheet
Dim PasteSheet As Worksheet

Set CopySheet = Worksheets("FXF CALL IN LOG")
Set PasteSheet = Worksheets("Complete")

PasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(2, 0).Value = Sheets("FXF CALL IN LOG").Range("I1").Value
CopySheet.Range("A2:I15").Copy
PasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

Sheets("Complete").Select
Sheets("Complete").Range("J1").Select
Sheets("FXF CALL IN LOG").Select
Sheets("FXF CALL IN LOG").Range("J4").Select

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

[vcoolio]

No worries Steve. Glad that I was able to help in some way.

Cheerio,
vcoolio.