Export Selected Excel Table Columns to a New XLSX Workbook with VBA
Question details
Export selected columns and headings from an Excel table to a new XLSX workbook as ordinary worksheet data, replacing the previous export.

- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A user needs a repeatable export containing only specific columns from an Excel table.
- Observed behavior
- The desired workbook contains the selected headings and values, with no unrelated worksheets or cells.
Save the source workbook as a macro-enabled file, and check the worksheet name, table name, and exact column headings. The macro saves LabelData.xlsx in the same folder and overwrites an existing file with that name.
Export selected table columns with an Excel VBA macro
Use this method to create a values-only XLSX export that can be regenerated whenever the source table changes.
The macro creates a workbook with one worksheet and copies the selected table headings and data as values. It includes hidden or filtered table rows but excludes the table's totals row.
In Excel, open the source workbook, press Alt+F11, and choose Insert > Module in the VBA editor.
Paste this code into the module: Sub ExportSelectedColumns() Dim sourceTable As ListObject Dim exportBook As Workbook Dim exportSheet As Worksheet Dim columnsToExport As Variant Dim i As Long Dim oldAlerts As Boolean Dim exportPath As String Dim errorMessage As String If ThisWorkbook.Path = "" Then MsgBox "Save the source workbook before exporting." Exit Sub End If oldAlerts = Application.DisplayAlerts On Error GoTo ExportFailed Set sourceTable = ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1") columnsToExport = Array("Name", "Address") exportPath = ThisWorkbook.Path & Application.PathSeparator & "LabelData.xlsx" Set exportBook = Workbooks.Add(xlWBATWorksheet) Set exportSheet = exportBook.Worksheets(1) For i = LBound(columnsToExport) To UBound(columnsToExport) exportSheet.Cells(1, i + 1).Value = sourceTable.ListColumns(columnsToExport(i)).Name If Not sourceTable.DataBodyRange Is Nothing Then exportSheet.Cells(2, i + 1).Resize(sourceTable.DataBodyRange.Rows.Count, 1).Value2 = _ sourceTable.ListColumns(columnsToExport(i)).DataBodyRange.Value2 End If Next i Application.DisplayAlerts = False exportBook.SaveAs Filename:=exportPath, FileFormat:=xlOpenXMLWorkbook Application.DisplayAlerts = oldAlerts exportBook.Close SaveChanges:=False MsgBox "Export saved to " & exportPath Exit Sub ExportFailed: errorMessage = Err.Description Application.DisplayAlerts = oldAlerts On Error Resume Next If Not exportBook Is Nothing Then exportBook.Close SaveChanges:=False On Error GoTo 0 MsgBox "Export failed: " & errorMessage End Sub
In the module, replace Sheet1 and Table1 with your worksheet and table names. Replace Name and Address in the Array with the exact headings to export, in the desired order.
Save the macro-enabled workbook, return to Excel, and choose Developer > Macros. Select ExportSelectedColumns and click Run.

Prepare selected columns with Power Query
Use Power Query when you want to record column selection and ordering without writing VBA.
Create a values-only column export in WPS Spreadsheets
Use WPS Spreadsheets to copy selected column headings and data into a new workbook, paste them as values, and save the result as an XLSX file.
- 1. Open the source workbook: In WPS Spreadsheets, choose Open and select the workbook containing your table.
- 2. Create a new workbook: Choose File > New and create a blank spreadsheet workbook for the export.
- 3. Copy the selected columns: In the source workbook, select a required column's heading and data cells and copy them. In the new workbook, use Paste Special > Values; repeat for each required column in the desired order.
- 4. Save the export: Use File > Save As to save the new workbook as an XLSX file. If the previous export has the same name, confirm replacement when prompted.

Frequently Asked Questions
How do I find the Excel table name for the macro?
Select a cell in the table and open the Table Design tab. Use the name shown in the Table Name box in place of Table1.
Will the VBA export include formulas or formatting?
No. The macro writes headings and cell values to the new workbook; it does not copy formulas or formatting.
Does the macro export rows hidden by a filter?
Yes. It reads the table's entire data body, including hidden and filtered rows. Exporting only visible rows requires a different approach.
Where is the exported XLSX file saved?
The macro saves LabelData.xlsx in the source workbook's folder. Save the source workbook first, or the macro will ask you to do so.
Can the Power Query export update automatically?
The query in the source workbook can be refreshed through Data > Refresh All, but the separate values-only XLSX file must be recreated or updated after each refresh.




