ExcelRibbon.Tips.Net ExcelTips (Ribbon Interface)

Transferring Data between Worksheets Using a Macro

Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Transferring Data between Worksheets Using a Macro.

Leonard is writing a macro to transfer data from one worksheet to another. Both worksheets are in the same workbook. The data he wants to transfer is on the first worksheet and uses a named range: "SourceData". It consists of a single row of data. Leonard wants to, within the macro, transfer this data from the first worksheet to the first empty row on the second worksheet, but he's not quite sure how to go about this.

There are actually several ways you can do it, but all of the methods have two prerequisites: The identification of the source range and the identification of the target range. The source range is easy because it is named. You can specify the source range in your macro in this manner:

Set rngSource = Worksheets("Sheet1").Range("SourceData")

Figuring out the first empty row in the target worksheet is a bit trickier. Here's a relatively easy way to do it:

iRow = Worksheets("Sheet2").Cells(Rows.Count,1).End(xlUp).Row + 1
Set rngTarget = Worksheets("Sheet2").Range("A" & iRow)

When completed, the rngTarget variable points toward the range of cell A in whatever the first empty row is. (In this case, an empty row is defined as any row that doesn't have something in column A.)

Now all you need to do is put these source and target ranges to use with the Copy method:

Sub CopySource()
    Dim rngSource As Range
    Dim rngTarget As Range
    Dim iRow As Integer

    Set rngSource = Worksheets("Sheet1").Range("SourceData")
    iRow = Worksheets("Sheet2").Cells(Rows.Count,1).End(xlUp).Row + 1
    Set rngTarget = Worksheets("Sheet2").Range("A" & iRow)
    rngSource.Copy Destination:=rngTarget
End Sub

Note that with the ranges defined, all you need to do is use the Copy method on the source range and specify the target range as the destination for the operation. When completed, the original data is still in the source range, but has been copied to the target.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6131) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Transferring Data between Worksheets Using a Macro.

Related Tips:

Organize Your Data! Using the powerful sorting capabilities of Excel can help you get your data into just the order you need. Find out how you can use the full capabilities of sorting to your benefit. Check out ExcelTips: Serious Sorting today!


Comments for this tip:

Buck    29 Apr 2013, 16:50
Thats perfect Barry. Thanks you.
Barry Fitzpatrick    29 Apr 2013, 05:55
The 1004 error unfortunately is isn't very specific, and without seeing all of the code its difficult to pinpoint the error. Try the following:

1. Close any other workbooks that you may have open.

2. Check/ensure that all variable are defined i.e for rngSource that there is a statement "Dim rngSource as Range", the named range "SourceData" does exist on Sheet1. Also ensure that none of the names has a trailing space(s) or spelling mistakes.

2. Use the Debug/Immediate window to ascertain if the range can be accessed by typing:

?Worksheets("Sheet1").Range("SourceData").Address <Enter>

this should return the address range of your source data, if not check the naming of the source range. If this is OK then the problem lies with the variable rngSource; probably with its definition.

3. substitute the actual address range for the SourceData name
Andreas M    29 Apr 2013, 01:15
Hi, I've been trying to use this code for hours and I keep getting a 1004 run-time error on this line:

 Set rngSource = Worksheets("Sheet1").Range("SourceData")

I've tried using this in both Excel 2007 and Excel 2010. Needless to say my sheets are named like in the example and I just can't imagine what is causing this error.

I would appreciate any help, thank you.
Barry Fitzpatrick    27 Apr 2013, 09:49

If I interpret what yo're saying correctly.

If you delete the line:

iRow = Worksheets("Sheet2").Cells(Rows.Count,1).End(xlUp).Row + 1

....and change the next line to:

Set rngTarget = Worksheets("Sheet1").Range("TargetRange")

where "TargetRange" is the named destination range on Sheet1 (or the name of whichever other sheet you want the values copied to).
Buck    26 Apr 2013, 12:18
I see that I can use Barry's modification to the code because it posts the values (not the formulas) to the target.

How can the code be modified to paste values to a named target in the current workbook rather than to the last cell?
Barry Fitzpatrick    26 Apr 2013, 08:46

I don't know if you've solved this problem yet.

The way to do this is to use the PasteSpecial method. and substitute the following:

rngTarget.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

for the the line:

rngSource.Copy Destination:=rngTarget

This change just pastes the values in all cells as the source values not formulas.
Christine Stimpson    27 Jan 2013, 12:18

Thank you for this. It was exactly what I was looking for and works beautifully with two problem cells.

Basically, I have an invoice template which I have set a macro to save and clear after finishing each invoice. What I also need it to do, is to copy key infomation off the invoice into an income details summary worksheet (in the same workbook) as a tracker. The above works with two expections. I need the actual data (date raised and invoice total), not the formulas. The date raised needs to remain static (I used. =today() and the value needs to be a figure.

The date dies display as todays date but come tomorrow, will be one day later and therefore not the correct date the invoice was raised.

I have named the two cells SourceData2 and SourceData6 respectively in sheet Invoice_Template, to be copied into columns E and D respectively in Income_Details.

Can anyone help with a solution please? I would be most grateful.

Thank you.
BParrott    13 Aug 2012, 12:45
  I have to copy around a hundred thousand NONCONSECUTIVE rows out of a list of several hundred thousand based on search criteria. So I'd like to use this tip in conjunction with my search criteria (hopefully I can also define my target range as a separate file). I could sort and filter my original list, but the size of it actually crashes my computer.
Tim Sandkuhler    12 Aug 2012, 23:44
You can also use UsedRange to find the last row. (below uses worksheet script)

LastRow = UBound(Me.UsedRange.Value)
Worksheets("Sheet1").Range("SourceData").Copy Destination:=Cells(LastRow + 1, 1)
Larry    11 Aug 2012, 17:21
Why not just copy and paste?
Willy Vanhaelen    11 Aug 2012, 09:18
When the macro resides in Sheet2, this simplified macro will do the job:

Sub CopySource()
Worksheets("Sheet1").Range("SourceData").Copy Destination:=Cells(Cells(1).End(xlDown).Row + 1, 1)
End Sub

Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 3+4? (To prevent automated submissions and spam.)
          Commenting Terms

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2013)

Our Products

Premium Newsletters

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2014 Sharon Parq Associates, Inc.