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:=xlDatab ase, 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

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



"Joel Mills" wrote in message
...

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:=xlDatab ase, 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

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 wrote:
I guess the pivot table baseitem can't be selected using VBA.



"Joel Mills" wrote in message
...


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:=xlDatab ase, 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






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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


"Debra Dalgleish" wrote in message
...
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 wrote:
I guess the pivot table baseitem can't be selected using VBA.



"Joel Mills" wrote in message
...


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:=xlDatab ase, 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






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html