I am trying to create a Pivot Table using a range that is stored in cell "A1" using the code below.
ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:= _
Range([indirect("a1")]).Value, Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:="", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion15
The Error is somewhere in the block above. I think it has something to do with how I am wanting to assign the SourceData. If I replace Range([indirect("a1")]).Value with what is stored in Cell A1 it will work but I need to be able to use the cell contents as my Ranges will vary.
This is what I have in Cell A1. Array(Array("'D0061'!R1C4:R65C52", "D0061"), Array("'D0411'!R1C4:R65C52", "D0411"), Array("'D0822'!R1C4:R65C52", "D0822"), Array("'D0999'!R1C4:R65C52", "D0999"))
I have just tried Range("A1").value, Cell("A1").value, assigned the value in A1 to a String Nothing has been working. Not sure what I am missing.
Any help would be appreciated.
Car
ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:= _
Range([indirect("a1")]).Value, Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:="", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion15
The Error is somewhere in the block above. I think it has something to do with how I am wanting to assign the SourceData. If I replace Range([indirect("a1")]).Value with what is stored in Cell A1 it will work but I need to be able to use the cell contents as my Ranges will vary.
This is what I have in Cell A1. Array(Array("'D0061'!R1C4:R65C52", "D0061"), Array("'D0411'!R1C4:R65C52", "D0411"), Array("'D0822'!R1C4:R65C52", "D0822"), Array("'D0999'!R1C4:R65C52", "D0999"))
I have just tried Range("A1").value, Cell("A1").value, assigned the value in A1 to a String Nothing has been working. Not sure what I am missing.
Any help would be appreciated.
Car