Filter and Export Excel Data Model PivotTable Data with VBA
Question details
Create a VBA macro that filters a Data Model PivotTable by individual year and month and exports selected columns to a new workbook.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Export columns A:K and Y:Z from PivotTable2 for each required year-month combination, with renamed output fields.
- Observed behavior
- Selecting multiple years or months at once can crash the workbook; the desired export omits the Grand Total row.
Open the source workbook in desktop Excel and confirm that PivotTable2 is on the worksheet named Capacity - constrained by sales. Use Developer > Record Macro while selecting one year and month to capture the exact Data Model field and member names.
Filter each period and export the PivotTable with VBA
Use this approach when the Data Model PivotTable must be filtered one year and one month at a time.
Replace the example field names and member identifiers with the exact strings from your recorded macro. The example uses a fixed list of year-month pairs because available Data Model members may not be reliably exposed through PivotTable items.
The macro creates one output worksheet per period in a new workbook. It copies values from columns A:K and Y:Z across the PivotTable's rows, then renames the last two output headers. It checks the final PivotTable row for the exact label Grand Total; adjust that label if the workbook uses a custom caption.
In Excel, select Developer > Record Macro, change the PivotTable year and month filters, and stop recording. Open Developer > Visual Basic and inspect the recorded VisibleItemsList statements for the exact PivotFields names and member identifiers.
In the Visual Basic editor, select the source workbook and choose Insert > Module. Paste the code below, then replace the field names and every example member identifier with the recorded values. Add one Array entry for each required period. Sub ExportPivotPeriods() Const YEAR_FIELD As String = "[Calendar].[Year].[Year]" Const MONTH_FIELD As String = "[Calendar].[Month].[Month]" Const TOTAL_LABEL As String = "Grand Total" Dim periods As Variant, item As Variant Dim src As Worksheet, pt As PivotTable Dim outBook As Workbook, outSheet As Worksheet Dim firstRow As Long, lastRow As Long, rowCount As Long Dim totalCell As Range, savePath As Variant Dim previousYear As String, sheetIndex As Long On Error GoTo Failed Set src = ThisWorkbook.Worksheets("Capacity - constrained by sales") Set pt = src.PivotTables("PivotTable2") periods = Array( _ Array("[Calendar].[Year].&[2024]", "[Calendar].[Month].&[2024-01]", "2024-01"), _ Array("[Calendar].[Year].&[2024]", "[Calendar].[Month].&[2024-02]", "2024-02") _ ) Set outBook = Workbooks.Add(xlWBATWorksheet) For Each item In periods If CStr(item(0)) <> previousYear Then pt.PivotFields(YEAR_FIELD).VisibleItemsList = Array(CStr(item(0))) previousYear = CStr(item(0)) End If pt.PivotFields(MONTH_FIELD).VisibleItemsList = Array(CStr(item(1))) firstRow = pt.TableRange1.Row lastRow = firstRow + pt.TableRange1.Rows.Count - 1 Set totalCell = pt.TableRange1.Rows(pt.TableRange1.Rows.Count).Find( _ What:=TOTAL_LABEL, LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) If Not totalCell Is Nothing Then lastRow = lastRow - 1 If lastRow <= firstRow Then Err.Raise vbObjectError + 1, , "No data rows for " & item(2) rowCount = lastRow - firstRow + 1 sheetIndex = sheetIndex + 1 If sheetIndex = 1 Then Set outSheet = outBook.Worksheets(1) Else Set outSheet = outBook.Worksheets.Add(After:=outBook.Worksheets(outBook.Worksheets.Count)) End If outSheet.Name = CStr(item(2)) outSheet.Range("A1").Resize(rowCount, 11).Value2 = src.Range("A" & firstRow & ":K" & lastRow).Value2 outSheet.Range("L1").Resize(rowCount, 2).Value2 = src.Range("Y" & firstRow & ":Z" & lastRow).Value2 outSheet.Range("L1").Value = "P cases" outSheet.Range("M1").Value = "U cases" Next item savePath = Application.GetSaveAsFilename( _ InitialFileName:="PivotExport.xlsx", _ FileFilter:="Excel Workbook (*.xlsx), *.xlsx") If savePath <> False Then outBook.SaveAs Filename:=CStr(savePath), FileFormat:=xlOpenXMLWorkbook MsgBox "Export saved." Else MsgBox "Export created but not saved. Choose File > Save As to keep it." End If Exit Sub Failed: MsgBox "Export stopped: " & Err.Description & vbCrLf & _ "Check the recorded field names, member identifiers, and sheet names." End Sub
Return to Excel, choose Developer > Macros, select ExportPivotPeriods, and click Run. In the Save As dialog, select the Desktop if desired and save the new workbook as an .xlsx file.
Open each output worksheet and check the period, header row, and last data row. Confirm that output columns L and M are labeled P cases and U cases and that no Grand Total row remains.

Review exported PivotTable data in WPS Spreadsheets
WPS Office is a free, lightweight Office alternative with a familiar interface. Open the exported workbook in WPS Spreadsheets to review and work with its values; migration is straightforward thanks to common Excel format support. It does not fix Excel Data Model or VBA filtering issues.

Frequently Asked Questions
How do I find the correct VisibleItemsList identifiers?
Choose Developer > Record Macro, select a single year and month in the PivotTable, and stop recording. Inspect the recorded code in Developer > Visual Basic and copy its exact field and member strings.
Why does the macro filter only one year and month at a time?
In this workbook, selecting multiple years or months can cause a crash. Each VisibleItemsList assignment therefore contains one member, and the export runs once for each configured period.
What if the Grand Total row still appears in the export?
Check the caption in the PivotTable's last row and update TOTAL_LABEL in the VBA module if it differs from Grand Total. If the total is not in the final row, adjust the row-detection logic before running the macro again.
Can the exported workbook contain the original VBA macro?
The example saves values to a new .xlsx workbook, which does not retain VBA code. Keep the macro in the source workbook and use File > Save As with a macro-enabled format if you need to run it again.
When should I use Set in VBA?
Use Set when assigning an object reference, such as Set src = ThisWorkbook.Worksheets("Capacity - constrained by sales") or Set X = Range("G2"). Do not use Set when assigning a cell value or text.




