Help with Pivot Table "BaseItem"

[Joel Mills]

Does anyone know of a way to select the "last" BaseItem in a pivot table. I
used the recorder and got this far.

I have a named range "Database" that uses CountA to determine the last
entry. The data comes from an export. The export will vary, but I will
always want to select the calculation on the pivot table to "PercentOf" and
will want the last entry to be the "BaseItem". Also if someone knows a
cleaner way to write what the recorder produced I would appreciate them
suggesting changes. I added the first line so that I could run the macro
from any worksheet.

I have been working on this project off and on for about a year now and have
gotten good feedback from the group.

Thanks,
Joel





Sub CummulativePivot()

Worksheets("Data").Activate
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Database").CreatePivotTable TableDestination:="",
TableName:="PivotTable1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Data", _
ColumnFields:="Week Ending"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Target Early" &
Chr(10) & "% Comp.")
.Orientation = xlDataField
.Position = 1
.Calculation = xlPercentOf
.BaseField = "Week Ending"
.BaseItem = "26-Sep-04"
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Target Late" &
Chr(10) & "% Comp.")
.Orientation = xlDataField
.Calculation = xlPercentOf
.BaseField = "Week Ending"
.BaseItem = "26-Sep-04"
End With
End Sub

 

[Joel Mills]

I guess the pivot table baseitem can't be selected using VBA.

 

[Debra Dalgleish]

If you're trying to use the last entry in the pivot data source as the
base item, you could use code similar to the following, where the Week
Ending field is in column A:

Dim strLastItem
Dim rngPivotData As Range
Set rngPivotData = Sheets("Data").Range("Database")
strLastItem = rngPivotData.Cells(rngPivotData.Rows.Count, 1).Value

With ActiveSheet.PivotTables(1).PivotFields("Target Early")
.BaseField = "Week Ending"
.BaseItem = strLastItem
End With

 

[Joel Mills]

Thanks, Debra that's what I wanted to do.

If you're trying to use the last entry in the pivot data source as the
base item, you could use code similar to the following, where the Week
Ending field is in column A:

Dim strLastItem
Dim rngPivotData As Range
Set rngPivotData = Sheets("Data").Range("Database")
strLastItem = rngPivotData.Cells(rngPivotData.Rows.Count, 1).Value

With ActiveSheet.PivotTables(1).PivotFields("Target Early")
.BaseField = "Week Ending"
.BaseItem = strLastItem
End With