vba pivot - Unable to create Pivot

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
874
Office Version
  1. 2010
Platform
  1. Windows
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"



VBA Code:
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
Are the sheets in the same workbook?
Or are you using two workbooks?
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
874
Office Version
  1. 2010
Platform
  1. Windows
Hi Mart37,

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



Thanks
mg
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Are you getting an error? If so, what is it and on which line?
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
874
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
Set pt = sht_Pivot.PivotTables(1) give an error when there are no pivottables. Delete this line.
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
874
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
Can't see errors. It works by me. Only a have to restart Excel once.

How do you call this function?
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,219
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top