Building a Pivot Table where column headings will change

[matpj]

I have a spreadsheet with columns from A to AE
The last 12 columns all contain months from the month AFTER the curren
month TO 12 months away.
e.g. nowm, they will show DEC05, JAN06, FEB06 .....etc

The rows contain a numerical value which represents the number of hour
put
The other two columns I am concerned about are C and D - colum
headings ALWAYS stay the same.

I need to create a pivot table automatically that will always use th
cooumn headings from the worksheet (whatever they are)
They'll exist in the same place, but obviously each month the pivo
table is created, the month headings will be different.

how can I do this?
Is it possible to create a pivot and point to a cell reference whe
telling it which columns to use??

thanks in advance..

 

[Roger Govier]

Hi

Your column headings have to be contiguous. You cannot have for example,
C,D,H,I,J,K.
You cannot leave a gap.
The data can come from a named range, and that range can be dynamic and
calculate the last column to use and/or the last row to use.
If you are saying you only want columns Cd and D plus 12 columns of months
in the future, could you not re-arrange your data so that the new columns
are inserted next to column D?

Alternatively, have a second sheet where this is the case and the data is
copied from Nov 06 to column D, from Oct 06 to column E etc. and use this
sheet as the source for your Pivot Table.

 

[philcud]

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

 

[matpj]

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


I just need to know how to tell it to use a column heading's cel
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 b
'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 wil
chang

 

[philcud]

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?

 

[matpj]

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

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

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

 

[matpj]

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.

 

[philcud]

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.

 

[DM Unseen]

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

 

[philcud]

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.

 

[DM Unseen]

The following pro's and con's ar my view:

SUMPRODUCT is

- leightweight
- formulas only

Pivottables

- drilldown
- external data source(but this can be partially mitigated with getting
the external data into an excel sheet first)
- OLAP source
- drag & drop customization
- nested hierarchies
- 'on the fly' slice & dice
- VBA support

when you only need a "simple" static pivottable with not much
interaction, SUMPRODUCT will do, in other cases a pivottable is better.

DM Unseen

 

[philcud]

yeah interaction and the wizard like drag and drop function are the
areas where it is clearly an avantage.

perhaps my POV is skewed due to the type of spreadsheets i build, and i
like to have more control over the format of charts (which is the
original reason i started using sumproduct over pivot tables.

BTW, what do you mean by lightweight?

 

[DM Unseen]

A pivottables takes more memory than a sumproduct for the same data and
pivottable size. Besides, it is also much more sensetive to the chance
of corruption(Pivot and query table objects are much more sensetive to
corruption than simple formula's).

Another thing I forgot to mention is refreshing, which is manual with
pivottables but can be automatic with SUMPRODUCT. Also SUMPRODUCT has
less compatability issues (although this will not be a big issue) with
conversions to other products/platforms.

DM Unseen