Dynamic pivot range

[VBABEGINER]

Appreciate highly, if any one help me in modify code this..

ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:= mypath & mybook1(Details!"R1C1:R&LstRowAC28"), Version:=xlPivotTableVersion14)

 

[mart37]

Re: how to give dynamic pivot range

strPivRange=mypath & "[" & mybook1 & "]Details!A1:AC" & LstRow
ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:= strPivRange, Version:=xlPivotTableVersion14)

 

[mart37]

Re: how to give dynamic pivot range

When you use an excel-table (format as table) you can use the tablename for a dynamic range.

 

[VBABEGINER]

Re: how to give dynamic pivot range

When you use an excel-table (format as table) you can use the tablename for a dynamic range.

Hi, Thanks for reply...But I really didn't get it. Could you pls giv me in code..

 

[mart37]

Re: how to give dynamic pivot range

When you use FORMAT AS TABLE the table gets the name: Table1
You can change this name as you like.

ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:= Table1, Version:=xlPivotTableVersion14)