How to copy Excel tables programmatically
The image above demonstrates a macro linked to a button. Press with left mouse button on the button and the macro runs. It copies the Excel table in cell range A1:D7 to cell range A13:D19.
What's on this page
- How to copy an Excel Table programmatically
- How to copy the contents of a filtered Excel Table programmatically
- How to copy multiple filtered Excel tables to a new worksheet
- How to apply a filter to all Excel tables in a workbook
- How to apply filters to all Excel tables and copy the result to a new sheet
- Clear all filters from all tables in the active workbook programmatically
- Where to put code?
- Get Excel *.xlsx file
1. How to copy an Excel Table programmatically?
The animated image above shows what happens when you press with left mouse button on the button. The VBA code below copies Table1 (Excel Table) to cell range A13 and adjacent cells.
Sub CopyTable() Range("Table1[#All]").Copy Destination:=Worksheets("2010").Range("A13") End Sub
2. How to copy the contents of a filtered Excel Table programmatically
The macro above works great if there are no filters applied to the table. If there were filters applied, all data would have been copied anyway.
So how do we solve that problem? This macro checks if each row in Table1 is visible. If it is, it is copied to a new sheet.
'Name macro Sub CopyFilteredTable() 'Dimension variables and declare datatypes Dim rng As Range Dim WS As Worksheet 'Go through rows in Table2 For Each Row In Range("Table2[#All]").Rows 'Check if row is visible If Row.EntireRow.Hidden = False Then 'The SET statement allows you to save an object reference to a variable, the image above demonstrates a macro that assigns a range reference to a range object. If rng Is Nothing Then Set rng = Row 'Returns the union of two or more ranges. Set rng = Union(Row, rng) End If 'Continue with next row Next Row 'Create a new worksheet Set WS = Sheets.Add 'Copy rng to cell A1 in worksheet WS rng.Copy Destination:=WS.Range("A1") End Sub
Documentation: Range | Rows | If | Row | EntireRow | Hidden | Nothing | Set | Union | Sheets.add | copy | For Next | For Each Next |
3. How to copy the contents of multiple filtered Excel tables to a new worksheet
The following macro iterates through each sheet in the current workbook and looks for Excel tables. It copies all visible values from every excel table to a new sheet.
This is handy if you have many excel tables in a workbook and you want to merge all filtered values from all tables to a new sheet.
'Name macro Sub CopyFilteredTables() 'Dimension variables and declare datatypes Dim WS As Worksheet Dim WSN As Worksheet Dim tbl As ListObject Dim rng As Range 'The SET statement allows you to save an object reference to a variable, the image above demonstrates a macro that assigns a range reference to a range object. Set WSN = Sheets.Add 'Go through each worksheet in active workbook For Each WS In Worksheets 'Go through Excel Tables in active worksheet For Each tbl In WS.ListObjects 'Clear rng object Set rng = Nothing 'Go through rows in Excel Table For Each Row In tbl.DataBodyRange.Rows 'Check if row is hidden If Row.EntireRow.Hidden = False Then 'Make sure rng object i nothing and then save row to rng If rng Is Nothing Then Set rng = Row 'Returns the union of two or more ranges. Set rng = Union(Row, rng) End If 'Continue with next row in Excel Table Next Row 'Save number of first non empty row to variable Lrow Lrow = WSN.Range("A" & Rows.Count).End(xlUp).Row 'Check that number is larger than 1 and if so add 1 to variable Lrow If Lrow > 1 Then Lrow = Lrow + 1 'Copy row rng.Copy Destination:=WSN.Range("A" & Lrow) 'Continue with next Excel Table Next tbl 'Continue with next worksheet Next WS End Sub
Documentation: Union | Nothing | If | copy | Row | EntireRow | Hidden | Set | ListObjects |
4. How to apply a filter to all Excel Tables in a workbook
The image above demonstrates a macro that applies the same filter to all Excel Tables in the workbook. This is useful if they all share the same Table header names and you want to save time not having to manually manipulate each Excel Table.
Cell range A1:D1 contains header names and cells below contain filter conditions.
Instructions
- Type the corresponding header, make sure you spell it right.
- Type the critera below each header
- Select the criteria with your mouse
- Run macro ApplyFilterToTable
'Name macro Sub ApplyFilterToTable() 'Dimension variables and declare datatypes Dim filters As Range Dim flV() As Variant 'The SET statement allows you to save an object reference to a variable, the image above demonstrates a macro that assigns a range reference to a range object. Set fltrs = Selection 'Redimension variable ReDim flV(0 To Selection.Rows.CountLarge - 2) 'Cycle through all sheets in active workbook For Each WS In Worksheets 'Cycle through all excel tables in sheet For Each tbl In WS.ListObjects 'Compare table headers to selection For ct = 1 To tbl.DataBodyRange.Columns.Count For cf = 1 To fltrs.Columns.Count 'Build array with filter values j = 0 For i = LBound(flV) To UBound(flV) If fltrs.Cells(i + 2, cf) <> "" Then flV(i) = CStr(fltrs.Cells(i + 2, cf)) Else flV(i) = "" j = j + 1 End If Next i 'Check if headers match If tbl.Range.Cells(1, ct) = fltrs.Cells(1, cf) Then 'Clear filter tbl.Range.AutoFilter Field:=ct 'Apply new filter If UBound(flV) <> j - 1 Then tbl.Range.AutoFilter Field:=ct, Criteria1:=flV, Operator:=xlFilterValues End If End If Next cf Next ct Next tbl Next WS End Sub
It cycles through all excel tables in a workbook and applies the filter you have selected on a sheet.
5. How to apply filters to all Excel tables and copy the result to a new sheet
So if you combine macro CopyFilteredTables and ApplyFilterToTable you can quickly apply multiple filters to all excel tables and then copy the filtered values from all excel tables to a new sheet. That saves you a lot of time.
Instructions
- Select the criteria. You also need to specify the corresponding header, make sure you spell it right.
- Run macro ApplyFilterToTable
- A new sheet is created and populated with values from all filtered
Sub ApplyCopy() 'Start macro named ApplyFilterToTable ApplyFilterToTable 'Start macro named CopyFilteredTables CopyFilteredTables End Sub
6. How to clear all filters from all Excel Tables in the active workbook
The image above shows an Excel Table with a filter applied, you can tell the Excel Table is filtered by the button next to the Table header name. The image below shows the same Excel Table
The macro below removes all filters in all Excel Tables in the active workbook.
'Name macro Sub ClearFiltersAllTables() 'Go through all worksheets For Each WS In Worksheets 'Got through all Excel Tables in Worksheet For Each tbl In WS.ListObjects 'Go through all columns For ct = 1 To tbl.DataBodyRange.Columns.Count 'Remove filter tbl.Range.AutoFilter Field:=ct 'Continue with next column Next ct 'Continue with next Excel Table Next tbl Next WS End Sub
Documentation: AutoFilter | DataBodyRange | ListObjects | Worksheets
7. Where to put code?

- Press Alt + F11 to open the Visual Basic Editor (VBE).
- Press with left mouse button on "Insert" on the menu.
- Press with left mouse button on Module to insert a new module to your workbook.
- Copy VBA code.
- Paste VBA code to the window, see image above.
- Exit VBE and return to Excel.
Table category
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]
This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]
This article demonstrates how to filter records occurring in only one out of two Excel defined tables. It also shows […]
The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]
This article describes a macro that hides specific columns automatically based on values in two given cells. I am also […]
This article demonstrates how to filter duplicate records using a simple formula and an Excel defined table.
I will in this article demonstrate a macro that copies criteria from one Excel Table and applies them to another […]
In this tutorial, I am going to demonstrate how to filter an Excel define Table through a VBA macro. How it […]
This article explains how to filter a data set based on extremely many conditions in an Excel defined Table, in […]
This blog post demonstrates how to filter unique distinct values from an Excel table dynamically. When you change or add […]
Today I am going to demonstrate how amazing pivot tables are! Take a look at this time sheet. You can […]
I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]
This article demonstrates how to insert and use a scroll bar (Form Control) in Excel. It allows the user to […]
An Excel table allows you to easily sort, filter and sum values in a data set where values are related.
This article demonstrates a macro that automatically applies a filter to an Excel defined Table based on the result from […]
The filter feature in Excel won't allow you to do OR logic between columns, however, you can if you allow […]
This article explains how to calculate the largest and smallest number based on a condition which is if the number […]
Macro category
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]
Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]
This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]
In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]
Today I would like to share with you these small event handler procedures that make it easier for you to […]
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]
This article describes different ways to locate literal or hardcoded values in formulas. The image above shows the result from […]
This article demonstrates macros that save worksheets to a single pdf file. What's on this webpage Export all worksheets in […]
How to use Excel Tables
Excel categories
5 Responses to “How to copy Excel tables programmatically”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
suppose you dont want the header to be copied
shelly
Oscar did you just copy and paste the same code?
I am trying to copy a filtered table but I don't want to paste the header information.
Hi, for copying filtered tables there seems to be a much easier solution:
as can be found here:
https://stackoverflow.com/questions/16039774/excel-vba-copying-and-pasting-visible-table-rows-only
hello, this was very useful, but how do I exclude tables ( I think I might be getting an error because I have named tables of different sizes- these ones I do not want to include in my filtered data )
thank you much