Setting up a new Pivot Table

[Downes51]

Hello there. I run a macro each week which analyses some data. It works fine until I try to stick the data into a Pivot Table
The size of the data range changes, so I give it a name and I'm trying to use the named range as the data source for the Table. I'm getting an error which I think relates to the SourceData statement....

The data range is selected, then.....
Dim PivRange As Range
Set PivRange = Selection
ActiveWorkbook.Names.Add Name:="PivRange", RefersToR1C1:=Selection
ActiveWorkbook.Names("PivRange").Comment = ""

'----> this next line (which was mostly from the Macro-recorder) won't execute;

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"'Loan Book'!PivRange", Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:="Loan Book!R2C26", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion15

Any ideas please?
John Downes

 

[mart37]

SourceData:= "'Loan Book'!" & PivRange.Address

 

[Downes51]

Thank you Mart. I've tried that, the line now reads;

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:="'Loan Book'!" & PivRange.Address, _
Version:=xlPivotTableVersion15). _
CreatePivotTable _
TableDestination:="Loan Book!R2C26", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion15



And when I run it and the macro reaches this line it's giving me the message "Invalid procedure call or argument." I'm sure the error is something trivial but I'm darned if I can see it!

I've tried moving the single quote marks around, I don't think that's it.

Regards,
John

 

[mart37]

My version of Excel had problems with: xlPivotTableVersion15
I have delete the refs to version.

 

[Downes51]

Thanks again Mart.
There might be something in that.. Issue with vba creating a pivot table, is this an excel version issue? - Stack Overflow
I'm running Excel 2013

Removing the version numbers leaves me with this;

Range(Cells(1, 1), Cells(LastRow, 24)).Select
Dim PivRange As Range
Set PivRange = Selection
ActiveWorkbook.Names.Add Name:="PivRange", RefersToR1C1:=Selection
ActiveWorkbook.Names("PivRange").Comment = ""

'The problematic line comes next;
ActiveWorkbook.PivotCaches. _
Create(SourceType:=xlDatabase, SourceData:="'Loan Book'!" & PivRange.Address) _
.CreatePivotTable TableDestination:="Loan Book!R2C26", TableName:="PivotTable1"

And guess what, I'm still getting the same error message.

John

 

[mart37]

I have test it in 2013 and it works.

Maybe make with the macrorecorder a new pivottable.
Replace the create line in your macro.
Test the changes.
Change the sourcedata-part.

 

[mart37]

TableDestination:="Loan Book!R2C26"
the single quotation mark are you forgotten.
Needed when a name have a space!

 

[Downes51]

Thanks Mart... well done. Working now.