How To Generate an Pivot using Macros

[Gokul001 (Gokul Balaji)]

Hello Everyone, Hope you are doing well

I would like to present this post who are looking for the expressions related to Create an Pivot using VBA script

Creating the Pivot using VBA

Option Explicit
Sub CreateNewSheet()
'
' CreateNewSheet Macro
'
'
   Dim wb As Workbook
   Dim ws As Worksheet
   Dim Name As String
   Dim SourceR As Range
   Dim TableName As String
   Set wb = ThisWorkbook
   Set SourceR = Range("A1").CurrentRegion
   TableName = "NewTable"
   Set ws = ActiveSheet
   With wb
   'On Error GoTo Errorhandler
   On Error Resume Next
    ws.ListObjects.Add(SourceType:=xlSrcRange, Source:=SourceR, XlListobjecthasheaders:=xlYes).Name = TableName
    If Err.Number > 0 Then
    MsgBox Err.Description
     MsgBox "Table already Created"
    Else
     On Error GoTo 0
    Debug.Print "Created Table Successfully ,Table name is" & " " & TableName
    If SheetExists(Name, wb, ws) = False Then
      wb.Sheets.Add After:=wb.Sheets(wb.Sheets.Count)
      ActiveSheet.Name = "PivotTable"
      Debug.Print "Sheet Created Successfully!"
    Else
      Debug.Print "Already created"
    End If
    Call CreateTableAndPivotTable(wb, ws, TableName)
     End If
   End With
   
Errorhandler:
   MsgBox "Table already Created!!"
End Sub
Function SheetExists(Name As String, wb As Workbook, ws As Worksheet) As Boolean
 For Each ws In wb.Worksheets
      Name = ws.Name
      If Name = "PivotTable" Then
        SheetExists = True
        Exit For
        Else
          SheetExists = False
        End If
    Next
End Function
Function CreateTableAndPivotTable(wb As Workbook, ws As Worksheet, TableName As String)
'
' CreateTableAndPivotTable Macro
'
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        TableName, Version:=6).CreatePivotTable TableDestination:= _
        "PivotTable!R1C1", TableName:="PivotTable2", DefaultVersion:=6
    Sheets("PivotTable").Select
    Cells(1, 1).Select
     With ActiveSheet.PivotTables("PivotTable2").PivotFields("Name ")
        .Orientation = xlRowField
        .Position = 1
     End With
     ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Age "), "Sum of Age ", xlSum
     With ActiveSheet.PivotTables("PivotTable2").PivotFields("Dept")
        .Orientation = xlRowField
        .Position = 2
     End With
     Debug.Print "Pivot Table Generated successfully"
End Function

Output

Hope this will be helpful

Questions

For questions on your retrieval case open a new topic and get individual support

Feedback

Click button to share your thoughts

Regards
Gokul