Written by Allen Wyatt (last updated September 28, 2019)
This tip applies to Excel 97, 2000, 2002, and 2003
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.
Note:
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: Copying Data between Worksheets Using a Macro.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2019 For Dummies today!
Excel allows you to specify the RGB (red, green, and blue) value for any color used in a cell. Here's a quick way to see ...
Discover MoreWhen you use macros to create functions, you might want to share those functions with others, particularly if they ...
Discover MoreVBA is a versatile programming language. It is especially good at working with string data. Here are the different VBA ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments