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: Inserting and Copying Rows.
Written by Allen Wyatt (last updated March 23, 2019)
This tip applies to Excel 97, 2000, 2002, and 2003
As you are editing worksheets, you may notice that some of your work is done based on work you have done before. For instance, you may have a row of data that you entered in a previous Excel session. In this session, you need to copy that row of data and use it as the basis for your new data, but with a few changes.
In such a situation, it would be nice to have a quick way to enter a blank row after the current row and copy the data in the current row to the new blank row. There are no intrinsic commands in Excel to do this, but a macro can do it very handily. Consider the following example:
Sub InsertCopyRow1() ActiveCell.EntireRow.Select Selection.Copy Selection.Insert Shift:=xlDown End Sub
In order to use the macro, all you need to do is select a cell in any row. When the macro is run, a duplicate of the current row is inserted just below the row you are in.
The only problem with this solution is that it leaves the Excel interface a bit "messy" (for lack of a better word). When completed, a complete row is still selected, and the new row has the "marching ants" marquee around it.
This problem can be overcome by including commands to collapse the selection and move it to a desired location. Another way is to simply use a different macro that relies on different VBA commands. The following macro will also insert and copy a row, but it leaves the cell that you selected active:
Sub InsertCopyRow2() ActiveCell.Offset(1, 0).EntireRow.Insert ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).EntireRow End Sub
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2042) 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: Inserting and Copying Rows.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Excel includes a handy shortcut for entering data that is similar to whatever you entered in the cell above your entry ...
Discover MoreHave you ever copied information from one worksheet to another, only to have the information you paste not look the way ...
Discover MoreCopy a formula from one cell to another on the same worksheet, and Excel adjusts any relative references in the formula ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2019-06-10 07:56:52
Paul Richardson
Hi Willy...
Thank you for your help.. It's great that there is more than one way to get to the solution!
2019-06-10 07:54:44
Paul Richardson
Hi Alan, thanks so much for your help with this.
Your conversation has helped me with other problems I have had. Thankfully now resolved!
2019-06-09 10:34:25
Willy Vanhaelen
@Paul Richardson
Your problem is very easy to solve. Simply copy the entire row and clear the contents of the cells in columns G and H in the new row so they are ready to receiven new data. This macro will do the job:
Sub InsertCopyRow()
With Selection
.EntireRow.Copy
.EntireRow.Insert
Range(Cells(.Row, "G"), Cells(.Row, "H")).ClearContents
End With
End Sub
2019-06-08 04:43:40
Hello Paul,
I tend to think of the Excel VBA Range.Insert method as primarily something that
…… makes a space to put new range in...............
If something happens to be in the clipboard when you use this code line, then VBA makes some assumption that you wanted what is in the clipboard put in the space. Exactly what it decides to do takes a book of explanation: It is left for us to figure out as there is no documentation that I know of
In the simplest case of having a single row in the clipboard, and using the Range.Insert to make a whole row as a space, then the results are as you expect, since the obvious choice that Excel makes is to assume you want to put that copied row in the new row space.
That is what Allen Wyatt’s first routine does. It inserts a new empty row at the active row ( or row containing the active cell ) , but also what is in the clipboard, ( the original row ) , is put into this new row. What actually is happening is that the original row has been shifted down. A copy of that original ( which was firstly made ) , is put in the new row space
In the second routine, he uses the Range.Insert method with nothing in the clipboard so that it just makes the row space, ( this time he kames that space one row down ). Then he copies the row and pastes it into the new row space.
So his two routines demonstrate well the point I am trying to make.
To copy just some of a row to a new row:
Using something similar to the second routine is the better alternative for only copying some of the original row to the new row space. ( The other alternative of trying to manipulate and then predict how the Range.Insert will handle a modified clipboard content is certainly possible and academically interesting, but might be a little advanced if you are VBA beginner, especially as the various Bugs and unknowns in the various Microsoft clipboards has been proven in recent years to be beyond the understanding of the Microsoft programmers themselves!! )
So, for example, the general idea would be
_1,
ActiveCell.Offset(1, 0).EntireRow.Insert Shift:=xlDown
or
ActiveCell.Offset(1, 0).EntireRow.Resize(1, 14).Insert Shift:=xlDown
_2, The simplest way, or first idea that springs to mind for me, is to copy the row info you want to a simple array, and then manipulate it to remove/ leave blank the info that you do not want, then paste that into the space.
I will contact you at the forum further as there is more space for exchanging codes and files etc.
Alan
2019-06-06 05:16:16
Hi Alan, thanks for your reply.
I run the code and can see what you mean.
My sheet is a basic project manager. (Sheet Name "ToDoList")
The sheet consists of Cols A : N
I would want to be able to copy any row (Active Row) from the sheet to paste and duplicate it by either:
1, inserting a row below the selected row.
2, Pasting it in the next available blank row in the same worksheet.
Number 1 would be the preferred solution.
The Cols I don't want copied, so left blank are G & H
The reason for this is that Col G is project status and H would be Completion date. And if I am creating a new project, I want these in an blank status.
Hope this makes sense.
Also the code you supplied has helped me with another small problem, so thanks again.. it is appreciated!
PS... I have left this thread on here, but I have also registered on your small forum.
2019-06-06 02:53:34
Hello Paul Richardson
VBA does allow you to copy parts of a row. This is because VBA in general will allow you to copy non contiguous cells, as long as they are “in line”.
So for example, any of these would work, ( The first one is copying some columns in a single row)
Range("A2,C2,H2").Copy
Range("A2:A4,C2:C4,H2:H4").Copy
Range("A1:A2,A5:A7,A19").Copy
Range("A1:B2,A12:B20").Copy
This however would not work:
Range("A2,C4,H2").Copy
The problem is that when you paste out any of the successful copied cells , VBA removes the spaces between.
To see what I mean put some random values in cells A2 C2 and H2 of the active worksheet, then run this
Sub Testie()
ActiveSheet.Range("A2,C2,H2").Copy
ActiveSheet.Paste Destination:=ActiveSheet.Range("A4")
Application.CutCopyMode = False ' Empty Excel Clipboard
End Sub
It would not be difficult to do what you want to do, but you would need a slightly more complicated code than that which Allen Wyatt has presented. It would need to loop through and copy and paste each column cell that you want copied. As often, there are almost infinite ways to do something like that in VBA. If you need more specific help then you would need to give a bit more specific details to which cells to be copied, where they are to be pasted etc…
( One of the larger Excel forums are usually the best place to ask such questions. But I don’t mind you asking me here or over at “my“ small forum, ExcelFox. )
Alan Elston
2019-06-04 06:25:25
Hi... Very new to VBA but getting there slowly. I have used your code above with great results, but I have now come across a problem. I want to copy the active row, but only copy a selection of the columns, not all of them. Sorry if this isn't the correct place to request this. Many thanks. TallPaulUK
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