Building a Pivot Table where column headings will change

maybe try the sumproduct function. i have a dislike of pivot tables,
and try to replace them with sumproduct functions where possible.

hi, the range is continuous, but on the pivot table i'm only displaying certain columns.


I just need to know how to tell it to use a column heading's cell value, rather than have a value hardcoded like this:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"TotalCombined!C1:C31").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable3"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable3").SmallGrid = False
ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:=Array( _
"Sheam Type", "Sheam Desc", "Data")
With ActiveSheet.PivotTables("PivotTable3").PivotFields("NOV05")
.Orientation = xlDataField
.Caption = "Sum of NOV05"
.Position = 1
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("DEC05")
.Orientation = xlDataField
.Caption = "Sum of DEC05"
.Position = 2
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("JAN06")
.Orientation = xlDataField
.Caption = "Sum of JAN06"
.Position = 3
.Function = xlSum
End With

basically, instead of having 'PivotFields("DEC05")' I want it to be 'PivotFields(<U1>)'

if that makes any sense. is it possible?

i've also got to define the range as a name, as the number of rows will change

if you try sumproduct (or even sumif if you only have one criteria) you
will be able to do this without code. is ther any reason you have to
use a pivot table?

i'll check out the sum product function - thanks for the suggestion.

doing it manually with a pivot table and some tweaking afterwards, is very easy and gives me the format that the 'table' has always been in (its for one of our directors, so i didn't really want to change the format.

its a shame this forum doesn't allow embedding of files, otherwise i'd show you what I have and what I need to acheive!!

i'm not sure SUMPRODUCT will solve my problem.

I do need a certain layout to the results - and also then add some data from another workbook to the top of it.
It requires grouping on two different levels and then summing the data in rows T to AE.

i'd really rather find out if its possible to specify a cell reference in the PivotTable VBA as mentioned above.

this is the line that is the problem:
With ActiveSheet.PivotTables("HighLevelFOB").PivotFields("T1")

I need to tell it to look at a named worksheet and take the contents of cell T1 to be the field.

one other idea is to build your pivot table as normal, then have
another table that uses the now function to define the last 12 month /
next 12 months and then use getpivot data function to get the relevant
months data out of the table. but i still think the sumproduct function
is the way to go. mail me your spreasheet and i'll try to put you in
the right direction.

This is not that hard:

The following code is something I currently use to solve exact this
issue. I have a querytable, and based on this several pivottables. All
columns after the column "Period Dayvalue Net" should be refreshed for
all pivottables. The following code does just that. The querytable is
on the sheet with codename "Details".

Sub RefreshPivotTables()
Dim rngCell As Range
Dim rngTarget As Range
Dim rngStartPeriods As Range
Dim intI As Integer
Dim sht As Worksheet
Dim pvt As PivotTable
Dim pvtfld As PivotField
Dim bIsRefresh As Boolean

Application.EnableEvents = False

' find the range of column names that need to be updated on all the
pivottables, they lie after the column "Period Dayvalue Net"
' find the column
Set rngStartPeriods =
Details.QueryTables(1).ResultRange.Rows(1).Find(What:="Period Dayvalue
Net", LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:= _
xlNext, MatchCase:=False).Offset(0, 1)
' extend the range to the end of the table
Set rngTarget = Range(rngStartPeriods,
rngStartPeriods.End(xlToRight))


On Error Resume Next
ThisWorkbook.PivotCaches(1).Refresh
bIsRefresh = (Err.Number = 0)
On Error GoTo 0

For Each sht In ThisWorkbook.Worksheets
For Each pvt In sht.PivotTables
If pvt.PivotCache.Index = 1 Then
intI = 1
pvt.ManualUpdate = True
If Not bIsRefresh Then pvt.RefreshTable
Application.StatusBar = "Clearing out PivotTable " &
pvt.Name
For Each pvtfld In pvt.DataFields
On Error Resume Next
pvtfld.Delete
If Err.Number <> 0 Then pvtfld.Orientation =
xlHidden
On Error GoTo 0
Next pvtfld

For Each rngCell In rngTarget.Cells
Application.StatusBar = "Adding Field " &
rngCell.Value & " to " & pvt.Name
With pvt.PivotFields(rngCell.Value)
.Orientation = xlDataField
.Position = intI
.NumberFormat = "#,##0_);[Red](#,##0)"
End With

intI = intI + 1

Next rngCell

pvt.ManualUpdate = False
pvt.RefreshTable
End If
Next pvt
Next sht

Application.EnableEvents = True
Application.StatusBar = False
End Sub

Dm Unseen

can i ask a question to the group : - when is a pivot table desired
over the sumproduct function?
please assume the user has the ability to do both correctly.
i am wondering whether i have been to hasty to try and get rid of pivot
tables, but removing them and replacing with sumproduct significantly
reduces workbook size, the output table always has the same data in the
same place, is the same size, it is much easier to format the resultant
charts, and the data is always up to date (a common error i have seen
is a pivot table not being refreshed). on the negative side, the
sumproduct function slows down workbooks if there are many of them.