Home > Macros > Basics > Excel Tables Copy Selected Items in Excel Table Use these Excel macros to copy selected rows in an Excel table, and paste at the end, or below the last selected item. Get the Excel file, so you can test the code, and copy it to your own workbooks. Author: Debra Dalgleish

Macros to Copy & Paste Table Rows

If you're adding new records to an Excel table, do you ever use this shortcut?

1. Find an existing record that's similar to the new data
2. Copy that existing record's row in the table
3. Go to the end of the table, and paste that record
4. Make minor changes to the pasted record, for the new data

Save Time With a Macro

To make your data entry shortcut go even faster, you can use one of the two VBA macros that I've created:

• Macro A) Pastes the copied records below the last row of the Excel table
• Macro B) Inserts new rows below the last selected item, and pastes the copied records in those new rows

There are macro details in the sections below, and an Excel file that you can download.

Test Macros in Sample Workbook

Before you test these macros in your own workbook, I recommend that you download the sample workbook, and try the macros there.

1) Open the Workbook

After you download the file, and unzip it, follow these steps:

• In Windows Explorer, go to the folder with the downloaded file
• Right-click on the file, and click Properties
• If there is an "Unblock" checkbox, at the bottom right corner, add a check mark, then click the OK button
• Open the workbook, and in the Security Warning bar, click the Enable Content button.

2) Check the Excel Table

Next, follow these steps, to check the Excel table:

• Go to the FoodSales sheet, where the Excel table is stored.
• Check that none of the rows or columns are hidden in the Excel table
  • The macros copy visible cells only - no data from hidden columns or rows is copied
• Check that the table starts in cell A1 -- if not, the macro code might need to be modified

3) Select Cells

Next, follow these steps, to get ready to run a macro:

• In the Excel table, select one cell in each row that you want to copy
  • In the screen shot below, I've selected a cell in rows 6, 9, and 10

Notes

• You can select a cell in any of the table's columns
• You can select cells in any order -- I selected B6, then B10, then C9.

4) Run a Macro

After the cells are selected, follow these steps to run one of the Copy/Paste macros:

• On the Excel Ribbon, go to the View tab
• At the right end of the tab, in the Macros group, click the top part of the Macros button
• In the Macro dialog box, click the arrow for the Macros In drop-down list
• From the list of workbooks, select This Workbook
• In the Macro Name list, click on one of the macros:
  • CopySelectionVisibleRowsEnd - copies the records, and pastes them at the end of the Excel table
• CopySelectionVisibleRowsInsert - copies the records, inserts new rows, pastes records in new rows
• Click the Run button

5) Find New Records in Excel Table

When you click the Run button the selected macro runs, copies the 3 selected records, and pastes them at the end of the table, or in new rows within the table.

• In the screen shot below, I ran the CopySelectionVisibleRowsEnd macro, and the three selected records were copied, and pasted at the end of the table.
• The table expanded automatically, to include the new records.

6) Edit New Records in Excel Table

In most cases, you'll want to make minor changes to the pasted records.

• For example, Store E147 orders these 3 items regularly -- the same products and quantities.
• In the new records, I'll just change the date, and everything else stays the same.

Use the Macros in Your Workbook

To use the macros in your Excel file, there are 3 main steps, with details in the sections below.

-- Step 1) Copy Macro Code Into Your Workbook

--Step 2) Save Workbook in Macro-Enabled Format

--Step 3) Make Backup Copy of Your File

Step 1) Copy Macros to Your Excel File

You can copy these macros from the sample file, or from the macro code sections below. Then, paste the copied code into a regular code module your own workbook.

Video: How to Copy Code to Regular Module

In this video, I show the steps for pasting a macro into a workbook, and then how to run the macro. There are written steps on the How to Add Code to an Excel File page.

Macro A) Copy Rows to End of Table

This macro, named CopySelectionVisibleRowsEnd, copies the selected rows, and pastes them below the last row in the Excel table.

Copy the Excel VBA code below and paste it into a regular code module in your workbook.

NOTE: Before running the CopySelectionVisibleRowsEnd macro, select one cell in each row that you want to copy. The macro will copy and paste the entire worksheet row for each cell that is selected.

Sub CopySelectionVisibleRowsEnd()
Dim ws As Worksheet
Dim mySel As Range
Dim lRow As Long
Dim lRowNew As Long
Dim lRowsAdd As Long
Dim myList As ListObject
Dim myListRows As Long
Dim myListCols As Long

Set ws = ActiveSheet
Set mySel = Selection.EntireRow
Set myList = ActiveCell.ListObject
myListRows = myList.Range.Rows.Count
myListCols = myList.Range.Columns.Count
lRow = ws.Cells.Find(What:="*", _
        SearchOrder:=xlRows, _
        SearchDirection:=xlPrevious, _
        LookIn:=xlValues).Row + 1

mySel.SpecialCells(xlCellTypeVisible).Copy
ws.Cells(lRow, 1).PasteSpecial _
		Paste:=xlPasteAll

lRowNew = ws.Cells.Find(What:="*", _
        SearchOrder:=xlRows, _
        SearchDirection:=xlPrevious, _
        LookIn:=xlValues).Row + 1
lRowsAdd = lRowNew - lRow

With myList
    .Resize ws.Range(.Range _
		.Resize(myListRows + lRowsAdd, 
			myListCols).Address)
End With

Application.CutCopyMode = False
End Sub

Macro B) Copy Rows Below Selection

This macro, named CopySelectionVisibleRowsInsert, inserts new rows in the table, below the last row where you selected an item. Then, the selected rows are pasted into those new rows.

Copy the Excel VBA code below, and paste it into a regular code module in your workbook.

NOTE: Before running the macro, select one cell in each row that you want to copy. The macro will copy and paste the entire worksheet row for each cell that is selected.

Sub CopySelectionVisibleRowsInsert()
Dim ws As Worksheet
Dim mySel As Range
Dim mySelVis As Range
Dim rngArea As Range
Dim lRowSel As Long
Dim lRowNew As Long
Dim lRowsAdd As Long
Dim c As Range
Dim lAreas As Long
Dim lRowsArea As Long

Set ws = ActiveSheet
Set mySel = Selection.EntireRow
lAreas = Selection.Areas.Count
lRowsArea = Selection.Areas(lAreas) _
	.Rows.Count
Set mySelVis = mySel _
	.SpecialCells(xlCellTypeVisible)

lRowSel = mySel.Areas(lAreas) _
	.Cells(lRowsArea, 1).Row + 1

For Each rngArea In mySel.Areas
  For Each c In rngArea.Columns(1).Cells
    If Not Intersect(c, mySelVis) _
		Is Nothing Then
      ws.Cells(lRowSel+1, 1) _
		.EntireRow.Insert
    End If
  Next c
Next rngArea

mySel.SpecialCells(xlCellTypeVisible).Copy
ws.Cells(lRowSel+1, 1).PasteSpecial _
		Paste:=xlPasteAll

Application.CutCopyMode = False
End Sub

Step 2) Save Workbook in Macro-Enabled Format

After you add the macro code to your workbook, be sure to save the file in a macro-enabled format -- xlsm or xlsb

Step 3) Make Backup Copy of Your File

Before you use either of the macros in your Excel file, be sure to make a backup copy of your workbook.

There is a free backup tool on this site that you can download and use. It's the same tool that I use with my Excel files, while I'm working on them.

The Quick Excel Backup Tool makes backup copy in the current folder, and doesn't affect the active workbook. The file is in xlam format, so it's easy to install on your computer - no registration process.

How the Macros Work

Here's a quick summary of how each of the macros work.

Macro A: How It Works

Before running the code, select one cell in each row that you want to copy. The macro will copy and paste the entire row for each cell that is selected.

• The macro counts the number of rows in the Excel table (myListRows) and the number of columns (myListCols).
• The macro calculates the row number for the last used row on the worksheet and adds 1 to that number, to determine where the rows should be pasted. (lRow)
• The selected rows are copied, then pasted into column A, in the row below the Excel Table --Cells(lRow, 1)
• After the rows are pasted, the macro re-calculates the row number for the last used row on the worksheet. lRowNew
• Then, it subtracts the old number from the new number, to determine how many rows were added. lRowsAdd = lRowNew - lRow
• Finally, the table is expanded to include the new rows. Resize(myListRows + lRowsAdd

Macro B) How It Works

Before running the code, select one cell in each row that you want to copy. The macro will copy and paste the entire worksheet row for each cell that is selected.

• The macro counts the number of different areas in the selected cells lAreas
• Next, it counts the number of rows in the last area lRowsArea
• The macro calculates the row number for the last row in the last area, and adds 1 to that number, to determine where the rows should be inserted and pasted. (lRowSel)
• Next, the macro loops through each area, and checks each cell in that area
  • For each cell that is visible, a row is inserted in the table
    ws.Cells(lRowSel, 1).EntireRow.Insert
• The selected rows are copied, then pasted into column A, in the newly inserted rows

More Excel Table Macros

You can find more macro examples on the Excel Table Macros page. Use these macros to automate the following tasks:

• List all the Excel tables in a workbook
• Get sheet names where tables are stored
• Fix a problem when a table does not expand automatically.

Get the Excel Files ✅

• Table Macro: Download the sample workbook with the two macros to copy and paste selected items. The zipped file is in xlsm format, and it contains macros.

Related Excel Tutorials

Named Excel Tables

Excel VBA Edit Your Recorded Macro

Excel VBA Getting Started

FAQs, Excel VBA, Excel Macros