VBA Coding for adding Pivot Table with changing data table

[darmst24]

Hello there,
Looking for some guidance on how to code into my macro adding a Pivot Table that has a variable data set.

I have the attached coding but does not work if the data set increases/decreases.

TIA!

Sub BuyerItemPivotTable()
'
' BuyerItemPivotTable Macro
'

'

    Range("B1:C1").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "NReport!R1C2:R8431C3", Version:=6).CreatePivotTable TableDestination:= _
        "Pivot Tables!R7C2", TableName:="PivotTable", DefaultVersion:=6
    Sheets("Pivot Tables").Select
    Cells(7, 2).Select
    With ActiveSheet.PivotTables("PivotTable").PivotFields("Buyer Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable").AddDataField ActiveSheet.PivotTables( _
        "PivotTable").PivotFields("Item Code"), "Count of Item Code", xlCount
End Sub

 

[saboh12617]

Hello,
Could you try to change the static reference in the source of the pivottable to the one of the selection, ie. replace :

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "NReport!R1C2:R8431C3", Version:=6).CreatePivotTable TableDestination:= _
        "Pivot Tables!R7C2", TableName:="PivotTable", DefaultVersion:=6

With

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Selection.Address, Version:=6).CreatePivotTable TableDestination:= _
        "Pivot Tables!R7C2", TableName:="PivotTable", DefaultVersion:=6

 

[NateSC]

It is faster to just find the last row and store it in a variable instead of "Select"-ing the cells. The below code will look for the last row with data in column 3 and return that row number in "LastRow". Then it uses that variable to form "PRange" which is then referenced in the Pivot creation. Just an alternate solution.

Dim LastRow as Long
Dim PRange as Variant

LastRow = Worksheets("NReport").Cells(Rows.Count, 3).End(xlUp).Row
PRange = "NReport!R1C2:R" & LastRow & "C3"

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        PRange, Version:=6).CreatePivotTable TableDestination:= _
        "Pivot Tables!R7C2", TableName:="PivotTable", DefaultVersion:=6

 

[darmst24]

Hello,
Could you try to change the static reference in the source of the pivottable to the one of the selection, ie. replace :

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "NReport!R1C2:R8431C3", Version:=6).CreatePivotTable TableDestination:= _
        "Pivot Tables!R7C2", TableName:="PivotTable", DefaultVersion:=6

With

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Selection.Address, Version:=6).CreatePivotTable TableDestination:= _
        "Pivot Tables!R7C2", TableName:="PivotTable", DefaultVersion:=6

Hi there, thanks for your help firstly.

I replaced coding as suggested but I am getting a debug - invalid argument

 

[saboh12617]

Hello again,

My mistake, you need to specify the sheet on which the range is, which is not done by default by the Address property. Also since you are using R1C1 style i would correct like this :

Selection.Address(,,xlR1C1,True)

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
Selection.Address(,,xlR1C1,True), Version:=6).CreatePivotTable TableDestination:= _
"Pivot Tables!R7C2", TableName:="PivotTable", DefaultVersion:=6

I have to point out the answer of NateSC is better VBA coding practice. But as i can tell you used the macro recorder, and in order to minimize the code modification, i did not corrected it. Also the online help PivotCaches.Create method (Excel) | Microsoft Learn recommands to use the range address instead of the range object for the SourceData argument.

 

[darmst24]

Hello again,

My mistake, you need to specify the sheet on which the range is, which is not done by default by the Address property. Also since you are using R1C1 style i would correct like this :

Selection.Address(,,xlR1C1,True)

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
Selection.Address(,,xlR1C1,True), Version:=6).CreatePivotTable TableDestination:= _
"Pivot Tables!R7C2", TableName:="PivotTable", DefaultVersion:=6

I have to point out the answer of NateSC is better VBA coding practice. But as i can tell you used the macro recorder, and in order to minimize the code modification, i did not corrected it. Also the online help PivotCaches.Create method (Excel) | Microsoft Learn recommands to use the range address instead of the range object for the SourceData argument.

Ok thank you, I'm struggling to get this code to work so i'll try the other coding. Many thanks

 

[darmst24]

It is faster to just find the last row and store it in a variable instead of "Select"-ing the cells. The below code will look for the last row with data in column 3 and return that row number in "LastRow". Then it uses that variable to form "PRange" which is then referenced in the Pivot creation. Just an alternate solution.

Dim LastRow as Long
Dim PRange as Variant

LastRow = Worksheets("NReport").Cells(Rows.Count, 3).End(xlUp).Row
PRange = "NReport!R1C2:R" & LastRow & "C3"

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        PRange, Version:=6).CreatePivotTable TableDestination:= _
        "Pivot Tables!R7C2", TableName:="PivotTable", DefaultVersion:=6

Hi There, thanks for your help!

I have tried your coding but I am getting the following error any suggestions?