Pivot Table Questions

[FROGGER24]

Is there away to make the ranges variable length-the number of columns will be the same

<ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R297C50", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion10
< Cells(3, 1).Select


When I run the macro that I recorded, the code stops on this segment of code--giving an error message "Unable to get PivotTables property of the worksheet class."
ActiveSheet.PivotTables("PivotTable1").PivotFields("Ship Or Barge"). _
CurrentPage = "Ship"

Appreciate any help that is given!!

 

[Jerry Sullivan]

The best approach in most situations is to use a Dynamic Named Range as the reference to the PivotTable's Datasource.

Contextures' Site has a good explanation on how to use Dynamic Named Ranges as the source for your PivotTables.

http://www.contextures.com/xlpivot01.html

The instructions are based on Excel 2003, but the formulas and concept works in later versions with merely different dialog windows to define names.