Copy new data with VBA

The other day I was helping someone on Stack Overflow with an interesting question: how can I copy new data from one worksheet to another? In other words, the user wanted to open a worksheet, add some data in one worksheet, then with the click of a button, copy the new data to another worksheet. There are a few interesting problems to solve with this so I thought it would be helpful to share the code with all of you in case you need to do something like this.

Let’s jump in.

The Problem

Simply put, the requirements are:

• Be able to copy new data from one worksheet to another
• The new data lives in column A of Sheet1 and needs to be moved to Sheet2
• The data should only be copied when the user clicks on a button

Let’s begin by creating the button, then adding the logic to copy data, and we’ll update the logic to copy new data.

Does this article help you? If so, please consider supporting me with a coffee ☕️

Setup

Let’s start by creating a new workbook.

Then, in Sheet1 A1 put Copy From. In Sheet2 A1, put Copy To. Add some formatting like the following:

Create a Button

We covered how to create a button and link it to a macro in this post. If you’re not familiar with how to do this, please check that out.

First, create a button in Excel on the worksheet that you want to copy the data:

Next, let’s have that button copy data from one worksheet to another.

VBA to Copy Data from Sheet1 to Sheet2

Create a new module and add the following code in there:

Option Explicit

Public Sub CopyNewData()
    Dim copyFrom As Range
    Dim copyTo As Range

    Set copyFrom = Sheets("Sheet1").Range("A2")
    Set copyTo = Sheets("Sheet2").Range("A2")

    copyFrom.Copy copyTo
End Sub

At the moment, we only copy Sheet1’s A2 to Sheet2’s A2. We will update this as we move along.

Assign the button to the new macro. Type something into Sheet1’s A2, then click the button and make sure your code is working.

OK, now that we have some working code, let’s keep iterating to get to our final solution.

Keeping Track of New Data

The next part we’ll focus on is how to keep track of new data.

We want to keep track of the new data in Sheet1, so let’s go to the VB Editor and double-click on Sheet1 in the Microsoft Excel Objects folder. This will bring us to the code behind the Sheet1 code.

Add this to the Sheet1 code module:

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("A2", "A" & sh.Rows.Count))

    If (Not newdata Is Nothing) Then
        Debug.Print "New Data in " & newdata.Address(0, 0)
    End If
End Sub

At the moment, this code uses the Worksheet_Change event (which we covered here) to inspect what has changed. The sub procedure is given a variable named target that tells us what cells were changed. It uses Intersect which is a function that we can use to see if the target’s range is “touching” the range that we care about (in our case, A2 and downward).

If it finds a match, it will print the address to the Immewiate Window (in the VB Editor, click on View -> Immediate Window).

Run this code to watch it work:

Notice that this will also capture events like deleting cell contents.

OK, now that we know how to capture new data and we know how to copy data from one worksheet to another, let’s combine these two and finish the solution.

Final Touches

There’s only one last problem to solve with this method: keeping track of new data in a variable. We don’t want to copy new data to the new worksheet until we click on the button, so we need to keep track of the new data into a variable somehow.

Global Variables

The solution is to use a global variable. Now, I’m not a big fan of global variables (because other modules can mess with your variables), but in this case it’s justified.

To create a global variable, you have to declare it outside of the sub procedure.

Go back to Module1 and add the global variable CopyNewDataRange like so:

Option Explicit

Public CopyNewDataRange As Range

Public Sub CopyNewData()
    Dim copyFrom As Range
    Dim copyTo As Range

    Set copyFrom = Sheets("Sheet1").Range("A2")
    Set copyTo = Sheets("Sheet2").Range("A2")

    copyFrom.Copy copyTo
End Sub

Notice that we use Public instead of Dim. We’ll cover that more in-depth in another post.

Now, let’s update our Worksheet_Change code to update that new variable with the new data.

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("A2", "A" & sh.Rows.Count))

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

Here, what we’re doing is:

• Checking to see if CopyNewDataRange is defined. If not, set it to the newdata range.
• If CopyNewDataRange is already defined, then add the newdata range cells to it with the Union function.

Finally, let’s update the button macro to use the CopyNewDataRange for copying the data:

Option Explicit

Public CopyNewDataRange As Range

Public Sub CopyNewData()
    Dim copyTo As Range
    Set copyTo = Sheets("Sheet2").Range(CopyNewDataRange.Address)

    CopyNewDataRange.Copy copyTo
End Sub

Here, we use the CopyNewDataRange as the range we are copying from and use the same address of that range for Sheet2 when we paste the data.

Let’s see it in action:

And that about does it for this post. I hope you learned something useful and please let me know in the comments below if you found ways to improve this code or have any questions about it. Thanks!