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
If you're adding new records to an Excel table, do you ever use this shortcut?
To make your data entry shortcut go even faster, you can use one of the two VBA macros that I've created:
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:
2) Check the Excel Table
Next, follow these steps, to check the Excel table:
3) Select Cells
Next, follow these steps, to get ready to run a macro:
After the cells are selected, follow these steps to run one of the Copy/Paste macros:
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 most cases, you'll want to make minor changes to the pasted records.
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.
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
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
After you add the macro code to your workbook, be sure to save the file in a macro-enabled format -- xlsm or xlsb
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.
Here's a quick summary of how each of the macros work.
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.
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.
You can find more macro examples on the Excel Table Macros page. Use these macros to automate the following tasks:
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.
Last updated: December 27, 2023 4:27 PM