Create Pivot Table Using VBA

1. Create a blank Excel Sheet and Save it in *.xlsm format .(Macro Enabled format).

2. Paste ur data in first sheet beginning from Row Number 6 ( data header should begin from 6th row)

3. Rename ur sheet to "Data" 

4. Create a Button by going to Developer tab > Insert > Active X control > Button , on the sheet. 

5. Right Click and go to properties > Name . Name button as "Btn_PivotTbl". 

6. Double click the button and Paste the code below between sub and end sub.



Dim LastRow As Long

Dim LastCol As Integer

Dim PivotRange As String

Dim sh As Worksheet



Rem this is to delete existing Pivot Sheet if it exists

Application.DisplayAlerts = False

For Each sh In ThisWorkbook.Worksheets

    If sh.Name = "Pivot" Then sh.Delete

Next sh

Application.DisplayAlerts = True



Sheets.Add.Name = "Pivot"



Rem Change "Data" to your sheet name

With Sheets("Data")    

   LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

End With



Rem Change "Data" to your sheet name

With Sheets("Data")   

    LastCol = .Cells(6, .Columns.Count).End(xlToLeft).Column

End With



If LastRow < 2 Then  

    MsgBox "No data found", vbCritical

    Exit Sub

End If





Rem Create Pivot

ActiveWorkbook.PivotCaches.Add(xlDatabase, PivotRange).CreatePivotTable "Pivot!R4C1", "PivotTable1"





7.Come back to excel file and exit design mode and click on button you created to run the code.