Home > Macros > Basics > Excel Tables Copy Selected Items in Excel Table Use these macros to copy selected items in an Excel table, and paste at the end, or below the last selected item. Get the free workbook to test the code.

Introduction

These macros will copy the selected items in an Excel table, and then:

• paste them below the last row of the Excel table
• OR, insert new rows below the last selected item, and paste them in the new rows

Before you run either macro:

• Unhide all columns. Because only the visible cells are copied, these macros will not copy and paste correctly if columns are also hidden.
• Select one cell in each row that you want to copy

Additional Notes:

• The macros select and copy the visible rows only, so if the table is filtered, the hidden rows will not be copied.
• The macros assume that the data is in a named Excel table that starts in cell A1. If the table starts in a different row or column, you can adjust the code to account for its location.
• Copy the macros to a regular code module in your workbook. There are instructions here if you aren't sure how to do that.

Copy Selected Items to End of Table

This macro copies the selected items to the end of the Excel table.

Read the notes in the Introduction, for tips on using this macro.

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

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

Copy Selected Items Below Selection

This macro copies the selected items to the row below the last selected item. New rows are inserted there, and the items are pasted into the new rows.

Read the notes in the Introduction, for tips on using this macro.

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 myList As ListObject
Dim myListRows As Long
Dim myListCols 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).EntireRow.Insert
    End If
  Next c
Next rngArea

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

Application.CutCopyMode = False
End Sub

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 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

Download the Sample File

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