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..

--
matp
-----------------------------------------------------------------------
matpj's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=48275

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.
--
Regards

Roger Govier


"matpj" wrote in
message ...

I have a spreadsheet with columns from A to AE
The last 12 columns all contain months from the month AFTER the current
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 hours
put
The other two columns I am concerned about are C and D - column
headings ALWAYS stay the same.

I need to create a pivot table automatically that will always use the
cooumn headings from the worksheet (whatever they are)
They'll exist in the same place, but obviously each month the pivot
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 when
telling it which columns to use??

thanks in advance...


--
matpj
------------------------------------------------------------------------
matpj's Profile:
http://www.excelforum.com/member.php...o&userid=21076
View this thread: http://www.excelforum.com/showthread...hreadid=482758

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

--
matp
-----------------------------------------------------------------------
matpj's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=48275

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, 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!

--
matp
-----------------------------------------------------------------------
matpj's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=48275

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").PivotField s("T1")

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


--
matpj
------------------------------------------------------------------------
matpj's Profile: http://www.excelforum.com/member.php...o&userid=21076
View this thread: http://www.excelforum.com/showthread...hreadid=482758

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.