How to export a PivotTable to a CSV file using VBA?

To export a PivotTable to a CSV file using VBA, you’ll need to loop through the PivotTable data and write it to a CSV file. Here’s a step-by-step guide to help you achieve this:

Sub ExportPivotTableToCSV()

    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim ptRange As Range
    Dim csvFileName As String
    Dim r As Long
    Dim c As Long
    Dim cellValue As String
    Dim outputLine As String
    
    ' Set the target worksheet and PivotTable
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name
    Set pt = ws.PivotTables("PivotTable1") ' Change to your PivotTable name
    
    ' Set the range of the PivotTable
    Set ptRange = pt.TableRange1
    
    ' Define the CSV file path
    csvFileName = ThisWorkbook.Path & "" & ""PivotTableExport.csv""

Open the Developer Tab: Make sure the Developer tab is visible in Excel. If not, go to “File” > “Options” > “Customize Ribbon” and check the “Developer” option.
Access VBA Editor: Click on “Developer” in the ribbon, then click on “Visual Basic” to open the VBA editor.
Insert a Module: In the VBA editor, right-click on any of the items in the “Project Explorer”, select “Insert”, and then “Module” to create a new module.
Write the VBA Code: Paste the following VBA code into the module. This code will export the PivotTable data to a CSV file.