Copy a Row in Excel VBA

Use Excel VBA to copy a row from one worksheet to another

Format
mla apa chicago
Your Citation
Mabbutt, Dan. "Copy a Row in Excel VBA." ThoughtCo, Nov. 30, 2016, thoughtco.com/copy-a-row-in-excel-vba-3424218. Mabbutt, Dan. (2016, November 30). Copy a Row in Excel VBA. Retrieved from https://www.thoughtco.com/copy-a-row-in-excel-vba-3424218 Mabbutt, Dan. "Copy a Row in Excel VBA." ThoughtCo. https://www.thoughtco.com/copy-a-row-in-excel-vba-3424218 (accessed October 20, 2017).
Calculation
ra-photos/E+/Getty Images

Using VBA to program Excel isn't 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 text
  • A numeric column - an automatic sum is created on the target worksheet
  • A 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, it's 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 End+Down Arrow).
  • Then, go back up to the last unused cell (equivalent to the End+Up 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 hasn't changed much.

Format
mla apa chicago
Your Citation
Mabbutt, Dan. "Copy a Row in Excel VBA." ThoughtCo, Nov. 30, 2016, thoughtco.com/copy-a-row-in-excel-vba-3424218. Mabbutt, Dan. (2016, November 30). Copy a Row in Excel VBA. Retrieved from https://www.thoughtco.com/copy-a-row-in-excel-vba-3424218 Mabbutt, Dan. "Copy a Row in Excel VBA." ThoughtCo. https://www.thoughtco.com/copy-a-row-in-excel-vba-3424218 (accessed October 20, 2017).