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.
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.
Let’s start by creating a new workbook.
A1 put Copy From. In
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
A2. We will update
this as we move along.
Assign the button
to the new macro. Type something into
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
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
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.
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.
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
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
CopyNewDataRangeis defined. If not, set it to the
CopyNewDataRangeis already defined, then add the
newdatarange cells to it with the
Finally, let’s update the button macro to use the
CopyNewDataRange for copying
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!