i have a data source which links to my sheet via a pivot table. on this pivot
table is the Date range the date starts at january 2005-present what i
require if its possible to minipulate the pivot tables to automatically
select may 2006 -July 2006 from list box.

can you send me some sample code if its posssible please because im not an
expert in pivot tables and dont no really how to start.

(my previouse code was created from a macro but the string of selections was
changed to strings gained from arrays (variants) which were linked to a
worksheet that worked them out from the selection made in the list)

"Debra Dalgleish" wrote:

You said that "these selections are fed into arrays as strings". I
suggested creating an array from the list on the user form, instead of a
string. Did you try that?

Then, .hiddenitemlist=showquart should work.

rivers wrote:
hi debra
I had already tried hiddenitemlist = showquart()
but this caused an error saying type miss match
and then tried
hiddenitemlist = Array(showquart)
which then gave a ore encouraging error
items were not found on OLAP cube


any idea?

"Debra Dalgleish" wrote:


You could create an array from the list, instead of a string. Then, make
the HiddenItemsList equal to the array.

rivers wrote:

hi im trying to automate a selection for date on all my pvot tables. i have
these pivots that are joined by an olap cube to my workbook. what i have done
is create diffrent tables for diffrent measures but i have one constant and
this is Date. im trying to create a user form which selects from a list the
months required for viewing.
eg March april May, 2006

these selections are fed into arrays as strings like
[Time].[Months].[All Time].[2006].[Quarter 3 2006].[July 2006]
i understand that with the Date field it requires a hide method for the
months not selected.

this is my code so far


ActiveSheet.PivotTables("PivotTable1").CubeFiel ds(44).TreeviewControl.Drilled
_
= Array(Array(""), array(showyear)), showquart)

ActiveSheet.PivotTables("PivotTable1").PivotFie lds("[Time].[Months].[Year]").
_
HiddenItemsList = Array(notyear)

ActiveSheet.PivotTables("PivotTable1").PivotFie lds("[Time].[Months].[Quarter]") _
.HiddenItemsList = notquart()

ActiveSheet.PivotTables("PivotTable1").PivotFie lds("[Time].[Months].[Month]"). _
HiddenItemsList = array(notmonth)

all the Arrays are previously set into strings on the workbook but the pivot
will not read my arrays as normal it comes up with errors and will not change
anything.

hope this reads alright

if anyones got a sample code that would be excellant!!!!!
i just want to be able to auto change 30 pivot tables to the date i chose in
a user format the start! sounds simple but as i found out, not so!

please help



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html