Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel VBA - Create Pivot - Error 91 Object Variable or With Block Variable Not Set 1

Status
Not open for further replies.

BxWill

MIS
Mar 30, 2009
367
US
My objective is to create several pivot tables using the same data source via vba.

Data is on worksheet "Data_sht" and the pivot table should display on the worksheet "Pivot_sht."

Note, the used range on "Data_sht" will change weekly (Number of rows and/or columns will change). Therefore, I plan to use another piece of code to dynamically change the data source range for every pivot table in the workbook.

The error that is received using the code below is "Object variable or With Block variable not set" and the line of code highlighted is
Code:
Set PT = PTCache.CreatePivotTable(TableDestination:=Pivot_sht.Range("A2"), TableName:="PivotTable1")

Have reviewed the code for over two hours and haven't determined the cause or resolution of the error.

Any insight as to a resolution of this error? Also, any additional thoughts as to the most efficient method to accomplish objective is appreciated.

Will continue to trouble shoot but thought that someone may be able to readily determine the solution...

Thanks in advance.

Code:
Sub CreatePivot_Iter2()

    Dim Data_sht As Worksheet 
    Dim Pivot_sht As Worksheet 
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PRange As Range
    Dim FinalRow As Long
    Dim FinalCol As Long 
      
    'Set Variables Equal to Data Sheet and Pivot Sheet
    Set Data_sht = ThisWorkbook.Worksheets("Data")
    Set Pivot_sht = ThisWorkbook.Worksheets("Pivot Table")
      
    ' Delete any prior pivot tables
    For Each PT In Pivot_sht.PivotTables
        PT.TableRange2.Clear
    Next PT
    
    ' Define input area and set up a Pivot Cache
    FinalRow = Data_sht.Cells(65536, 1).End(xlUp).Row
    FinalCol = Data_sht.Cells(1, Application.Columns.Count).End(xlToLeft).Column
       
     Set PRange = Data_sht.Cells(1, 1).Resize(FinalRow, FinalCol)  'original line
     Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange.Address) 'Iter1
     
     Set PT = PTCache.CreatePivotTable(TableDestination:=Pivot_sht.Range("A2"), TableName:="PivotTable1")
     
     PT.ManualUpdate = True
        
    ' Set up the row  & column fields
     PT.AddFields RowFields:=Array("Product", "Customer"), ColumnFields:="Region"
        
    ' Set up the data fields
    With PT.PivotFields("Revenue")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 1
    End With
    
    ' Calc the pivot table
    PT.ManualUpdate = False
    PT.ManualUpdate = True
    
    ' Added for the project files
    Pivot_sht.Select
        
End Sub
 
Hi,

As you stated, your UsedRange on Data_sht will change.

So you should forget creating a new PT each time.

Rather simply assign the NamedRange a Named Range. Database is a good name. Turn on your macro recorder and name the range. Then call that code in the Worksheet Change event. The code will need modification to run properly. Also change the PT source data to Database and your problem is solved with minimal effort.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top