Pivot Table Fail in VBA

[pd2004]

Hello,

Can anyone tell me why this code doesn't run:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data_All_Items_Added!R3C1:R13174C8", Version:=xlPivotTableVersion10). _
CreatePivotTable TableDestination:="PVT_Items_Added!R3C1", TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion10

I get the following error message:

Run-time error '5'

Invalid procedure call or argument.

I believe I have checked the names of the source and destination sheets. I am running windows 7 for the first time, and I am wondering if that is an issue, or if I need a reference. anyway, please help if you can. thank you, Pat.

 

[SkipVought]

hi,

Where did you get this code?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pd2004]

Hi Skip,

Nice to hear from you again. I hope all is well. I copied this code from a module that I had made previously. I also recorded it. I have been working with it for a while, and I am really confused as to why it doesn't work. I sense that you know the answer. If the code is really bad, I apologize. If you can point me in the right direction I would appreciate it. As I say, I am running windows 7 now, which is a change.

Thank you,

Pat

 

[SkipVought]

I copied this code...
I also recorded it.

Does that mean that at some time in the past, you RECORDED it, and the subsequently you COPIED it?

Or how do these two statements correlate?


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pd2004]

Hello Skip,

That is likely the case. A while ago, when I was using windows XP and likely an older version of Excel, I was working a lot more with pivot tables and trying to automate some reporting that uses them. Normally I do most of that work in Excel, but sometimes it is better for our users to provide a pivot. Anyway, I copied that code and am tryng to use in another module, now running windows7, and Excel 2010. I have been recording and modifying, trying to make it work. I would prefer to pass parameters into it for source of the data and destination of the pivot, but I tried using the actual addresses to make it simpler. Anyway, if you can help, I appreciate it. If not, I appreciate all of the help you have given already. Have a nice day.

Pat

 

[SkipVought]

Then you need to RECORD the macro in the version that you are running.

HOWEVER, I question WHY you would be ADDING a PivotTable to the same sheet REPEATEDLY? That does not make sense!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pd2004]

Hi Skip,

I thought that was the problem initially as well. However, I do not think so. the same code fails in my module. then on the same page I can record the exact same macro, and it runs. I understand your point about adding the same pivot more than once, but I don't think that is happening. Anyway, I appreciate your attention and I will keep working on it. I will let you know if I can resolve. I am thinking it is an issue with the references selected in the VBA editor.

Thank you,

Pat

 

[SkipVought]

But THAT is the problem!

You record adding a PivotTable, called [highlight #FCE94F]PivotTable1[/highlight].

Then, when you RERUN that macro, it tries to add [highlight #FCE94F]PivotTable1[/highlight]!

Do ya see the [highlight #FCE94F]problem[/highlight]???



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pd2004]

Hi Skip,

It kind of seems like you are unhappy with my efforts. I totally understand what you are saying. I was onto that very early on. The problem is, you have the two in reverse order. First, my code fails trying to make Pivottable1. Then, on the same sheet, I record the macro to make pivottable1. I am likely doing somehting foolish, and I will let you know when I figure it out.

Thank you,

Pat

 

[SkipVought]

Here's a version that works on my PC. You may need to change the sheet names, but notice hwe I use 2 sheet objects,

the ActiveSheet (ws) is where the PT Source Data is in A1

The new PT is in wsPT at A3

'
    Dim wsPT As Worksheet, ws As Worksheet
    
    [highlight #FCE94F]Set ws = ActiveSheet[/highlight]
    
    [highlight #8AE234]Set wsPT = Sheets.Add[/highlight]    

    ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=[highlight #FCE94F]ws.Cells(1, 1).CurrentRegion[/highlight], _
        Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:=[highlight #8AE234]wsPT.Cells(3, 1)[/highlight], TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion12
        
    wsPT.Cells(3, 1).Select

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pd2004]

Thank you, Skip. I really appreciate all of your help!
Pat

 

[SkipVought]

Sorry that I didn't pay close enough attention to the details in which the devil inhabits.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pd2004]

You're the best Skip! I appreciate your help always.

Thank you again.

Pat