vba pivot - Unable to create Pivot

[Mallesh23]

Hi Team,

I am unable to create New pivot table in summary sheet. below is my attempted code.


Below are the variable which stores inofrmation.
?NewRange
'Debtors Report'!R4C1:R41451C58.... Source
?rng_destination -- Pivot trying to store here.
'Summary'!R3C1

?wb.name
Debtors Report Apr 19 to Jan 21_22.01.2021.xlsb


Set pt = pc.CreatePivotTable(rng_destination, "Debtor Summary Report")................ pt shows nothing here
pt.AddFields (Array("Responsibility", "Remarks")), "Ageing Bucket"

Sub Refresh_Pivot(ByVal sht_Source As Worksheet, ByVal wb As Workbook, ByVal sht_Pivot As Worksheet)

        Dim LastCol As Long
        Dim rng_source As Range
        Dim LastRow As Long
        Dim NewRange As String
        Dim pt As PivotTable
        Dim pf As PivotField
        Dim key1 As Range
        Dim pc As PivotCache
        
        Dim rng_destination As String
        


    With sht_Source
        LastCol = sht_Source.Cells(4, .Columns.Count).End(xlToLeft).Column
        LastRow = sht_Source.Cells(.Rows.Count, 1).End(xlUp).Row
        Set rng_source = .Range(.Cells(4, 1), .Cells(LastRow, LastCol))
     End With


        NewRange = "'" & sht_Source.Name & "'!" & _
        rng_source.Address(ReferenceStyle:=xlR1C1)
        
        rng_destination = "'" & sht_Pivot.Name & "'!" & sht_Pivot.Range("A3").Address(, , xlR1C1)
        
    
    Set pt = sht_Pivot.PivotTables(1)
    
    
    Set pc = wb.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=NewRange)
   
    
    '---------Delete Pivot-----------
    For Each pt In sht_Pivot.PivotTables
        pt.TableRange2.Clear
    Next pt
    
      
Set pt = pc.CreatePivotTable(rng_destination, "Debtor Summary Report")

      pt.AddFields (Array("Responsibility", "Remarks")), "Ageing Bucket"


With pt.PivotFields("Outstanding Balance")
   .Orientation = xlDataField
   .Function = xlSum
   .Caption = "Sum of Outstanding Balance"

end sub

Thanks
mg
End With

 

[mart37]

Are the sheets in the same workbook?
Or are you using two workbooks?

 

[Mallesh23]

Hi Mart37,

its in same workbook, I am deleting existing pivot and Creating new pivot in its Place.



Thanks
mg

 

[RoryA]

Are you getting an error? If so, what is it and on which line?

 

[Mallesh23]

Hi Rory,

Error Message is :===> Object variable or block variable not set. err no - 91

Line no :-----> pt.AddFields (Array("Responsibility", "Remarks")), "Ageing Bucket"

I think Pivot is not created, pt is showing nothing.


Thanks
mg

 

[mart37]

Set pt = sht_Pivot.PivotTables(1) give an error when there are no pivottables. Delete this line.

 

[Mallesh23]

Hi Mart,

I appending new record to previous workbook and there will be always pivot, I tried commenting that line and tested

still not getting correct output. can you check are my object pc and pt are correct. Thanks.


Thanks
mg

 

[mart37]

Can't see errors. It works by me. Only a have to restart Excel once.

How do you call this function?

 

[mart37]

Sub test4()
Refresh_Pivot Worksheets("Debtors Report"), Worksheets("Summary")
End Sub

Sub Refresh_Pivot(ByVal sht_Source As Worksheet, ByVal sht_Pivot As Worksheet)
    Dim LastCol As Long
    Dim rng_source As Range
    Dim LastRow As Long
    Dim NewRange As String
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim key1 As Range
    Dim pc As PivotCache
    Dim rng_destination As String
    With sht_Source
        LastCol = sht_Source.Cells(4, .Columns.Count).End(xlToLeft).Column
        LastRow = sht_Source.Cells(.Rows.Count, 1).End(xlUp).Row
        Set rng_source = .Range(.Cells(4, 1), .Cells(LastRow, LastCol))
     End With
    NewRange = "'" & sht_Source.Name & "'!" & _
    rng_source.Address(ReferenceStyle:=xlR1C1)
    rng_destination = "'" & sht_Pivot.Name & "'!" & sht_Pivot.Range("A3").Address(, , xlR1C1)
'---------Delete Pivot-----------
    For Each pt In sht_Pivot.PivotTables
        pt.TableRange2.Clear
    Next pt
    Set pc = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=NewRange)
    Set pt = pc.CreatePivotTable(rng_destination, "Debtor Summary Report")
    pt.AddFields Array("Responsibility", "Remarks"), "Ageing Bucket"
    With pt.PivotFields("Outstanding Balance")
       .Orientation = xlDataField
       .Function = xlSum
       .Caption = "Sum of Outstanding Balance"
    End With
End Sub