Hi
I have a number of pivot tables that have filters to be slected by the user,
but I want to auto select the value using field values held in another
worksheet, Currrently when the user selects a value form the other worksheet
(using a drop down box) various calculations are done and this prompts teh
users to select certain values in the other worksheets.

I have tried the follwoing code which sometimes works but at other times it
doesn't and being a complete VBA novice I don't know what I'm doing wrong

Sheets("Contract totals").Select
ActiveSheeet.PivotTables("Shares_select").Pivotfie lds
("Netreins").CurrentPage.Name = Range("X2")


It doesn't seem to like the Range("X2") which is a field on the spreadsheet
that is linked to a value on the source worksheet. I would rahter it point to
the actual other worksheet file but I don't know how to achive that.

Anyone any ideas?

regards
John

Assume the sheet with the pivottable is named PTbleSheetname and the sheet
with the source data is named SourceSheetName. Then you could do:

Worksheets("PTbleSheetName").PivotTables( _
"Shares_select").Pivotfields _
("Netreins").CurrentPage.Name = _
Worksheets("SourceSheetName").Range("X2").Value



--
Regards,
Tom Ogilvy


"JohnC" wrote:

Hi
I have a number of pivot tables that have filters to be slected by the user,
but I want to auto select the value using field values held in another
worksheet, Currrently when the user selects a value form the other worksheet
(using a drop down box) various calculations are done and this prompts teh
users to select certain values in the other worksheets.

I have tried the follwoing code which sometimes works but at other times it
doesn't and being a complete VBA novice I don't know what I'm doing wrong

Sheets("Contract totals").Select
ActiveSheeet.PivotTables("Shares_select").Pivotfie lds
("Netreins").CurrentPage.Name = Range("X2")


It doesn't seem to like the Range("X2") which is a field on the spreadsheet
that is linked to a value on the source worksheet. I would rahter it point to
the actual other worksheet file but I don't know how to achive that.

Anyone any ideas?

regards
John

Hii Tom
Many thanks for your reply.

I have used your code and it is now working although hI have made a slight
amendemnt to your code. I have removed the .Name after the CurrentPage as I
ws getting a 'Run time error 1004
'Unable to set the Name property of the PivotItem class
I don't know why but found the answer by trial and error....hopefully!


One other question for anyone. Is it possible to test that the value the
PivotField is being changed to actually exists in the listing without the
macro bombing out?

Regards
John




"Tom Ogilvy" wrote:

Assume the sheet with the pivottable is named PTbleSheetname and the sheet
with the source data is named SourceSheetName. Then you could do:

Worksheets("PTbleSheetName").PivotTables( _
"Shares_select").Pivotfields _
("Netreins").CurrentPage.Name = _
Worksheets("SourceSheetName").Range("X2").Value



--
Regards,
Tom Ogilvy


"JohnC" wrote:

Hi
I have a number of pivot tables that have filters to be slected by the user,
but I want to auto select the value using field values held in another
worksheet, Currrently when the user selects a value form the other worksheet
(using a drop down box) various calculations are done and this prompts teh
users to select certain values in the other worksheets.

I have tried the follwoing code which sometimes works but at other times it
doesn't and being a complete VBA novice I don't know what I'm doing wrong

Sheets("Contract totals").Select
ActiveSheeet.PivotTables("Shares_select").Pivotfie lds
("Netreins").CurrentPage.Name = Range("X2")


It doesn't seem to like the Range("X2") which is a field on the spreadsheet
that is linked to a value on the source worksheet. I would rahter it point to
the actual other worksheet file but I don't know how to achive that.

Anyone any ideas?

regards
John