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: Copying Data between Worksheets Using a Macro.
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.
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!
Macros can be used to change the formatting of your worksheet, if desired. One change you might want to make is to the ...
Discover MoreWhen creating a macro, one of the ways you can communicate with users is through the use of a message box. This tip ...
Discover MoreClick a button on a toolbar and Excel will go so far as to open a another workbook in order to run a macro associated ...
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 © 2024 Sharon Parq Associates, Inc.
Comments