Pivot Table Object Error. 1

[TRHorn]

Hello,

I'm writing a little piece of code for something I'm doing at work and I'm having trouble with a persistent error.
I'm taking data from 5 sheets on a work book, combining it and then creating a pivot table. I have the first part, but the Pivot table is giving me some trouble. I don't know much about the pivottable object so I just used the "record macro" feature.

The second line of my recorded code is giving me an error. It says Object defined or application defined error. I have bypassed it using the on error handler but then it doesn't do any of the other actions besides create the initial pivot table template. Any help would be appreciated.



Range("B9").Select

'Error begins here
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= "Sheet1!R6C1:R14C14").CreatePivotTable TableDestination:="[Book1]Sheet2!R2C2", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

'Error ends here

ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _
"PCT Eng. Division", "Year", "PCT Lead")
ActiveSheet.PivotTables("PivotTable1").PivotFields("PCT Eng. Hours"). _
Orientation = xlDataField
With ActiveSheet.PivotTables("PivotTable1").PivotFields("PCT Eng. Division")
.PivotItems("BTS").Visible = False
End With

 

[SkipVought]

Hi,

Do you want to add a pivot table each time you run this?

'Error begins here
    ActiveWorkbook.PivotCaches.Add( _
        SourceType:=xlDatabase, _
        SourceData:="Sheet1!R6C1:R14C14").CreatePivotTable _
        TableDestination:="[Book1]Sheet2!R2C2", _
        TableName:="PivotTable1", _
        DefaultVersion:=xlPivotTableVersion10

'Error ends here

your SourceData sheet/range reference will have to change each time.
your TableDestination must be correct and unique.
your TableName must be unique.

Please explain how this code should work functionally.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TRHorn]

I Guess I should have posted more. This code will be modified to find the length of the data I am selecting each time and before it is called the previous pivot table will be deleted, actually everything on the sheet it goes on will be deleted.

So Yes I do want to add a pivot table everytime I run this. I think my deletion of all the previous pivots will take care of your concerns, not sure though, haven't got that far.

 

[SkipVought]

and before it is called the previous pivot table will be deleted,

WHY? Is it really necessary to delete and add? Frankly, I use PTs regularly and RARELY if ever delete and add.

You can easily CHANGE the source data/range. and refresh the pivot cache. This can all be accomplished with very little VBA code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TRHorn]

It's not necessary. I just want to refresh it I suppose. As I said I don't really have any knowledge of the PT object. Could you show me a way of doing this?

 

[SkipVought]

Turn on your macro recorder and record refershing your PT.

Post back with your recorded code if you need help customizing.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

With activeworkbook.pivotcaches(1).pivottables("TableName"))
.sourcedata:= "SourceDataLocationHere"
.refresh
end with

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[TRHorn]

Problem Solved, I used the pivottablewizard method to reset the range an then I just refreshed the data.

Thanks for the replies.