CODEWITHSUNDEEP
vbaexcel
alex2002
alex2002

Reputation: 161

Range copy-paste vba

The code below is transfering the values from column A to column D. When copied from column A, the values are always pasted on the next row empty in column D.

Is it possible to make this code to copy the values from column A only when new values are inserted? because what it does now, it takes all the values from column A and paste them again in column D.

i.e. Column A: a1 = 2; a2 = 3, a3 = 4 ---> Column D becomes: d1 = 2; d2 = 3; d3 = 4.

now if I insert two other values in column A: a4 = 1; a5 = 2, I want only those two values two be transfered in column D, and not the all numbers again. So column D becomes d1 = 2; d2 = 3; d3 = 4, d4 = 1; d5 = 2.

Thanks in advance!

Private Sub CommandButton3_Click()

Application.ScreenUpdating = False

With Sheets("Sheet1")
     .Range("a5", .Range("a5").End(xlDown)).Copy
End With

With Sheets("Sheet1")
     .Range("d" & .Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
End With

End Sub

Upvotes: 0

Views: 525

Answers (2)

Joseph
Joseph

Reputation: 5160

Based on the requirements:

  • Only copy new data
  • Has to be copied when user clicks a button

Here's what you could do:

In the Worksheet that the button / data lives on:

Option Explicit

Private Sub Worksheet_Change(ByVal target As Range)
    Dim sh As Worksheet
    Set sh = ActiveSheet

    Dim newdata As Range
    Set newdata = Application.Intersect(target, sh.Range("A5", "A" & sh.Rows.Count))

    If (Not newdata Is Nothing) Then
        If (CopyDataRange Is Nothing) Then
            Set CopyDataRange = newdata
        Else
            Set CopyDataRange = Application.Union(CopyDataRange, newdata)
        End If
    End If
End Sub

In a Module:

Option Explicit

Public CopyDataRange As Range

Public Sub CommandButton1_Click()
    If (Not CopyDataRange Is Nothing) Then
        CopyDataRange.Copy CopyDataRange.Offset(0, 3)
        Set CopyDataRange = Nothing
    End If
End Sub

What this will do is keep track of the new values only (this is what the Worksheet_Change event does), then when you click the button, it will copy only the new values to column D. It also resets the static range CopyDataRange back to Nothing so you don't repeat copying the same data from previous button clicks. Essentially, the button click will flush out the CopyDataRange range.

EDIT:

I decided to write a post about this here to explain it further.

Upvotes: 2

alex2002
alex2002

Reputation: 161

As you guys said that it is a bit complicated, i modified the code and what it does now is to copy everything from the beginning. Not sure if this is the smart solution to use...

Private Sub CommandButton3_Click()

Application.ScreenUpdating = False
     With Sheets("Creation")
        .Range("a1", .Range("a1").End(xlDown)).Copy End With

With Sheets("Creation")
        .Range("d1").PasteSpecial Paste:=xlPasteValues

End With End Sub

Upvotes: 0

Related Questions