Pivot Table Macro with dynamic source data
 

We've upgraded to Excel 2010. In past macros, I've been able to make
a pivot table dynamic with this script (below) and swapping out the
set range for Range(A1).

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
Range("A1").CurrentRegion.Address).CreatePivotTabl e
TableDestination:="", TableName:= _
"PivotTable3", DefaultVersion:=xlPivotTableVersion10


Now the script is different with

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase,
SourceData:= _
"DL!R1C1:R163C16",
Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet4!R3C1", TableName:="PivotTable2",
DefaultVersion _
:=xlPivotTableVersion10

If I add Range ("A1"), in place of "DL!R1C1:R163C16" I get an error
message. Any ideas?

Pivot Table Macro with dynamic source data
 

Can you post the exact code you're getting the error with, and the
number+text of the error?

Tim

On Feb 9, 7:24*am, Corrie wrote:
We've upgraded to Excel 2010. *In past macros, I've been able to make
a pivot table dynamic with this script (below) and swapping out the
set range for Range(A1).

* * ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
* * * * Range("A1").CurrentRegion.Address).CreatePivotTabl e
TableDestination:="", TableName:= _
* * * * "PivotTable3", DefaultVersion:=xlPivotTableVersion10

Now the script is different with

* * ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase,
SourceData:= _
* * * * "DL!R1C1:R163C16",
Version:=xlPivotTableVersion10).CreatePivotTable _
* * * * TableDestination:="Sheet4!R3C1", TableName:="PivotTable2",
DefaultVersion _
* * * * :=xlPivotTableVersion10

If I add Range ("A1"), in place of "DL!R1C1:R163C16" I get an error
message. *Any ideas?