Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Transferring Data between Worksheets Using a Macro.
by Allen Wyatt
(last updated September 28, 2019)
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 (3852) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Transferring Data between Worksheets Using a Macro.
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!
Your company may be regulated by requirements that it document any changes to the macros in an Excel worksheet. Your ...Discover More
Don't need that old macro any more? Here's how to get rid of it so that it is no longer a part of your workbook.Discover More
Figuring out where Windows places certain items (such as the user's desktop) can be a bit frustrating. Fortunately, there ...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 menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.