Changing pivot datasource by formulas?

[Snoopy]

Hi guys
I not quite sure if there is any solution to this.
My last chance is to cry for help in this room of qualifications...
(aaaargh!!)

This monthly "cut-off" report of mine is based on one chosen dataset
(the last monthly) - placed in each sheet in my workbook; 09-Jan, 09-
Feb, 09-Mar ... and so on.
New month makes a new sheet, one dataset pr month / all sets with
identical columnheaders.

In the sheet "Montly Report" I have a pivot-table using the latest
monthly dataset.
My problem is that I have to manually change the pivot-dataset to
change report of one month to another.

In some other matters I make use of the formula expression: =CELLE
("filname";A1);6) to change sheet-references in formulas, but i think
this is not a possible option when using menudriven prosedure.

Yes I know I can make one big multi-month dataset, but there are some
options regarding conditional formats (among other issues) in the
dataset to allow for.

Is there any option to change the pivot-datasheet-reference by using
formulas? - or by using macros?

Expectant regards
Snoopy

 

[dheaton]

Hi guys
I not quite sure if there is any solution to this.
My last chance is to cry for help in this room of qualifications...
(aaaargh!!)

This monthly "cut-off" report of mine is based on one chosen dataset
(the last monthly) -  placed in each sheet in my workbook; 09-Jan, 09-
Feb, 09-Mar  ... and so on.
New month makes a new sheet, one dataset pr month / all sets with
identical columnheaders.

In the sheet "Montly Report"  I have a pivot-table using the latest
monthly dataset.
My problem is that I have to manually change the pivot-dataset to
change report of one month to another.

In some other matters I make use of the formula expression: =CELLE
("filname";A1);6) to change sheet-references in formulas, but i think
this is not a possible option when using menudriven prosedure.

Yes I know I can make one big multi-month dataset, but there are some
options regarding conditional formats (among other issues) in the
dataset to allow for.

Is there any option to change the pivot-datasheet-reference by using
formulas? - or by using macros?

Expectant regards
Snoopy

Hi,

Record a macro while you change the pivot table data range manually.
Once you have amended the pivot table, stop recording and view the
macro.
You should see something like this...

ActiveSheet.PivotTables("PivotTable1").ChangePivotCache
ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R16C6:R18C12")

thats how to change the data range of the table.

To incorporate that into your macro try this


Sub ChangeRange()

dim DataRange as string
DataRange=Worksheets("The Name of the sheet with the pivot table
on").Range("d1")
ActiveSheet.PivotTables("PivotTable1").ChangePivotCache
ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=
DataRange)
End Sub


then you just enter the range (including sheet name) of the data range
in d1 and run the macro.

You could expand this further using data validation lists or a listbox
that would negate the need to manually enter the address.

HTH