Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. 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.
by Allen Wyatt
(last updated September 17, 2016)
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, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Transferring Data between Worksheets Using a Macro.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Unprotecting a single worksheet is relatively easy. Unprotecting a whole lot of worksheets is harder. Here's how you can make ...Discover More
If you have a macro that selects different columns in a worksheet while processing information, you may get some undesired ...Discover More
Make your macros too long, and Excel may just refuse to run them at all. This tip explains what the limit is for macros, and ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.