How to Copy a Row in Excel VBA

How to Copy a Row in Excel VBA Using VBA to program Excel isnt as popular as it once was. However, there are still plenty of programmers who prefer it when working with Excel. If you are one of those people, this article is for you.​ Copying a row in Excel VBA is the kind of thing that Excel VBA is really useful for. For example, you may want to have one file of all your receipts with date, account, category, provider, product/service, and cost entered one line at a time, as they occur- an instance of evolving accounting rather than static accounting. To do this, you need to be able to copy a row from one worksheet to another. A sample Excel VBA program that copies a row from one worksheet to another- using only three columns for simplicity- contains: An alpha column for textA numeric column - an automatic sum is created on the target worksheetA date column - the current date and time is filled in automatically Considerations for Writing Excel VBA Code To trigger an event that copies the row, go with the standard- a Button form control. In Excel, click Insert on the Developer tab. Then, select the Button form control and draw the button where you want it. Excel automatically displays a dialog to give you a chance to select a macro triggered by the click event of the button  or to create a new one. There are several ways to find the last row in the target worksheet so the program can copy a row at the bottom. This example chooses to maintain the number of the last row in the worksheet. To maintain the number of the last row, you have to store that number somewhere. This might be a problem because the user might change or delete the number. To get around this, place it in the cell directly underneath the form button. That way, its inaccessible to the user. (The easiest thing to do is enter a value in the cell and then move the button over it.) Code to Copy a Row Using Excel VBA Sub Add_The_Line() Dim currentRow As Integer Sheets(Sheet1).Select currentRow Range(C2).Value Rows(7).Select Selection.Copy Sheets(Sheet2).Select Rows(currentRow).Select ActiveSheet.Paste Dim theDate As Date theDate Now() Cells(currentRow, 4).Value CStr(theDate) Cells(currentRow 1, 3).Activate Dim rTotalCell As Range Set rTotalCell _ Sheets(Sheet2).Cells(Rows.Count, C).End(xlUp).Offset(1, 0) rTotalCell WorksheetFunction.Sum _ (Range(C7, rTotalCell.Offset(-1, 0))) Sheets(Sheet1).Range(C2).Value currentRow 1 End Sub This code uses xlUp, a magic number, or more technically an enumerated constant, which is recognized by the End method. Offset(1,0) simply  moves up one row in the same column, so the net effect is to select the last cell in column C. In words, the statement says: Go to the last cell in column C (equivalent to EndDown Arrow).Then, go back up to the last unused cell (equivalent to the EndUp Arrow).Then, go up one more cell. The last statement updates the location of the last row. VBA is probably harder than VB.NET because you have to know both VB and Excel VBA objects. Using xlUP is a good example of the kind of specialized knowledge that is critical to being able to write VBA macros without looking up three different things for every statement you code. Microsoft has made great progress in upgrading the Visual Studio editor to help you figure out the correct syntax, but the VBA editor hasnt changed much.