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:

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!


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 5+3 (To prevent automated submissions and spam.)
           Commenting Terms

Comments for this tip:

Barry    30 Mar 2016, 08:40

To have a "Submit" button simply insert a button control on Sheet1 size and position it where you want, change the caption to "Submit" and assign the macro to it. These controls are in the "Developer" ribbon which is hidden by default when Excel is first installed, this will need to be made visible via the Options menu.

Bigger Don answered the question about clearing the input cells on 06 Nov 2014 13:07.

The copy routine doesn't do any data validation so the User can enter any old rubbish and it will be transferred. It is good programming practice to validate input data to avoid future issues.
chariti    29 Mar 2016, 22:25

How can I create a macro in excel that takes information entered on sheet 1(my template) moves that data to subsequent sheets in a workbook and then deletes the data in sheet1 or my template. Can this be done by using a button named submit?

I see that Mark F asked this question but I do not see where it was answered.
Thank you,
Carlos    14 Mar 2016, 10:55
Dear friends
I have situation....
On the sheet1 I have many rows and I need to copy from sheet1. At the Column "A" (Y/N). I need conditional (if A:A="Y", copy Column "B,C,D,E,F" to Sheet2 next blank row.
Thank you very much.
Erika    06 Jul 2015, 18:39
The code works great for me, but I'm still having a slight problem. I'm trying to copy about 7 pieces of data from one tab into another tab, where the 2nd tab is an archive table of the data. The data going into column A is pasting correctly but every column after that is pasting an extra row down. Do you have any suggestions on how to fix this? Thanks!
Mark F    06 Nov 2014, 19:30
@Bigger Don perfect!
Bigger Don    06 Nov 2014, 13:07
@Mark F

Try rngSource.ClearContents between the Copy and the End Sub...

    rngSource.Copy Destination:=rngTarget
End Sub
Mark F    06 Nov 2014, 00:12
Hi Allen,

the macro at the top works great for me but I want to add a function that deletes the original data in sheet one.

I am a beginner and can't quite get this to work

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

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–2016)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.