Trying to create a pivot table with a macro

Hi Jenny,

PFB for the required code and PFA for the sample data and macro file. I hope this code helps.

Option Explicit

Sub creating_pivot_tables()


Application.ScreenUpdating = False

Dim pivottable1 As PivotTable
Dim pivotField1 As PivotField
Dim last_row As Integer
Dim range1 As Range

'code for deleting pivot table in activesheet, if exist
For Each pivottable1 In ActiveSheet.PivotTables
        ActiveSheet.Range(pivottable1.TableRange2.Address).Delete
Next pivottable1
    
last_row = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
Set range1 = ActiveSheet.Range("A1:R" & last_row)
    
    

    
Range("A1").Select
Selection.AutoFilter

ActiveSheet.AutoFilter.Sort.SortFields.Add Key:=Range _
    ("M1:M" & last_row), SortOn:=xlSortOnValues, Order:=xlAscending         'sorting data in column M
    
With ActiveSheet.AutoFilter.Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
    
    Selection.AutoFilter
        
    'applying first pivot on the data
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        range1, Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:="'[creating pivot tables.xlsm]Sheet3'!R2C21", TableName:= _
        "Pivot1", DefaultVersion:=xlPivotTableVersion15
    

    'Adding division column
    ActiveSheet.PivotTables("Pivot1").AddDataField ActiveSheet.PivotTables( _
        "Pivot1").PivotFields("DIVISION"), "Count of DIVISION", xlCount
        
    With ActiveSheet.PivotTables("Pivot1").PivotFields("DIVISION")
        .Orientation = xlRowField
        .Position = 1
    End With
    
    'selecting and copying existing pivot table
    ActiveSheet.PivotTables("Pivot1").PivotSelect "", xlDataAndLabel, True
    Selection.Copy
    
    'creating second pivot table
    ActiveSheet.Range("X2").PasteSpecial
    
    Application.CutCopyMode = False
    
        
    'renaming second pivot table
    ActiveSheet.PivotTables(1).Name = "Pivot2"

    'adding vendor name column
    With ActiveSheet.PivotTables("Pivot2").PivotFields("Vendor Name")
        .Orientation = xlRowField
        .Position = 2
    End With
    
    ActiveSheet.PivotTables("Pivot2").PivotFields("DIVISION").LayoutForm = _
        xlTabular
    
    ActiveSheet.PivotTables("Pivot2").PivotSelect "DIVISION[All;Total]", _
        xlDataAndLabel, True
        
    
    'removing subtotal from second pivot table
    With ActiveSheet.PivotTables("Pivot2")
        For Each pivotField1 In .PivotFields
            pivotField1.Subtotals(1) = False
        Next pivotField1
    End With
    
    Range("A1").Select
    
        
End Sub

Regards
Ramandeep Singh

Hello!
Thank you very much for your reply! I will be back at work on Monday and will give it a try then. I'll be sure to come back and let you know how it goes.

Jenny