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.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.