VBA Copy Paste pasting value incorrectly to adjacent column

Question

I am trying to copy paste two columns from one sheet to another but running the code copies old value to adjacent cell if I run the VBA code more than 1 time

 Sub CopyData()
Dim i As Integer


i = Worksheets("SP_Download").Cells(Rows.Count, 6).End(xlUp).Row

If i = 1 Then i = 2

Worksheets("SP_Download").Range("F2:F" & i).Delete


i = Worksheets("DataPull").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("DataPull").Range("A2:A" & i).Copy
Worksheets("SP_Download").Range("F2:F" & i).PasteSpecial Paste:=xlPasteValues

i = Worksheets("SP_Download").Cells(Rows.Count, 6).End(xlUp).Row
Worksheets("SP_Download").Range("I2:I" & i).Delete

i = Worksheets("DataPull").Cells(Rows.Count, 5).End(xlUp).Row
Worksheets("DataPull").Range("E2:E" & i).Copy
Worksheets("SP_Download").Range("I2:I" & i).PasteSpecial Paste:=xlPasteValues


Application.CutCopyMode = False


End Sub

Intended Data pull after successful code run is this:

but I end up getting this:

Here data gets incorrectly copied to column G & H incorrectly. Can you please help me figure out why this is happening

Thanks

The code you provided would not do this, are there worksheet events or formula that would add the values? — Scott Craner
Hello Scott ! thanks for helping out. There is no worksheets events or formulas setup.When I run the VBA code the first time I get the data copied correctly on Column "I" (as shown in the first image) but each time I run the code again it copies the value in Clipboard to the adjacent column.... I don't know what i am doing incorrectly here. — Mysticleaf

coross24 coross24 · Accepted Answer · 2020-09-03T19:53:45

I copied your code over and I saw the same error. When stepping through with F8, unfortunately it was a copy-paste error when deleting columns. Unfortunately, the copy-paste method is quite error prone and would avoid it if possible! One alternative method is to set one range equal to the other. I've amended your code using this method and it seems to work now:

Sub CopyData()

Dim i As Integer

' clear existing data
i = Worksheets("SP_Download").Cells(Rows.Count, 6).End(xlUp).Row
If i > 1 Then Worksheets("SP_Download").Range("F2:I" & i).ClearContents

' copy first set of data
i = Worksheets("DataPull").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("SP_Download").Range("F2:F" & i) = Worksheets("DataPull").Range("A2:A" & i).Value2

' copy second set of data
i = Worksheets("DataPull").Cells(Rows.Count, 5).End(xlUp).Row
Worksheets("SP_Download").Range("I2:I" & i) = Worksheets("DataPull").Range("E2:E" & i).Value2

End Sub

Good luck!

VBA Copy Paste pasting value incorrectly to adjacent column

2 Answers