Bloated Macro Creates Pivot Table

[Dathan Camacho]

Hi, this is the first time I've created pivot tables in VBA.

I started the code below by adapting a recorded macro. It runs, but I suspect it still has unnecessary, redundant, or ineficient bits since it came from the recorder.

Can you help me put it on a diet so to speak, for educational purposes?

[SIZE=3][FONT=Calibri]With Worksheets("A")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]            .Range("A4:K" & LastRow).Select[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End With[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]         Dim x As String[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]         x = Selection.Address[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]         Dim y As Integer[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]         y = 1[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=x, TableDestination:="A!R1C17", TableName:="PivotTable" & y[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]          With ActiveSheet.PivotTables("PivotTable" & y).PivotFields("Type")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              .Orientation = xlPageField[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              .Position = 1[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End With[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]With ActiveSheet.PivotTables("PivotTable" & y).PivotFields("Date")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              .Orientation = xlRowField[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              .Position = 1[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End With[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]ActiveSheet.PivotTables("PivotTable" & y).CalculatedFields.Add "Field1", _[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              "=1-'Numerator' /'Denominator'", True[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]          ActiveSheet.PivotTables("PivotTable1").PivotFields("Field1").Orientation = _[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              xlDataField[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]With ActiveSheet.PivotTables("PivotTable" & y).PivotFields("Sum of Field1")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              .Caption = "Average of Field1"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              .Function = xlAverage[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End With[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]With ActiveSheet.PivotTables("PivotTable" & y)[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]          .ColumnGrand = False[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]          .RowGrand = False[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End With[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]ActiveSheet.PivotTables("PivotTable" & y).PivotFields("Type").CurrentPage = "T"[/FONT][/SIZE]

 

[jbeaucaire]

Maybe:

With Worksheets("A")
    .Range("A4:K" & LastRow).Select
End With
 
Dim RNG As String
Dim y As Long

RNG = Selection.Address
y = 1
 
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=RNG, TableDestination:="A!R1C17", TableName:="PivotTable" & y
    With ActiveSheet.PivotTables("PivotTable" & y)
        With .PivotFields("Type")
            .Orientation = xlPageField
            .Position = 1
        End With
        With .PivotFields("Date")
            .Orientation = xlRowField
            .Position = 1
        End With
 
        .CalculatedFields.Add "Field1", "=1-'Numerator' /'Denominator'", True
        .PivotFields("Field1").Orientation = xlDataField
 
        With .PivotFields("Sum of Field1")
            .Caption = "Average of Field1"
            .Function = xlAverage
        End With
 
        .ColumnGrand = False
        .RowGrand = False
        .PivotFields("Type").CurrentPage = "T"
    End With