Pivot Table
 

Hi,

Sheet3.PivotTables("PivotTable2").PivotFields("Typ e").CurrentPage = _
"Intercooler"

I have a macro which links to nine other workbooks which are identical
in layout. The macro loops through all nine workbooks and enters data from
the same place in each workbook into Sheet1 in my workbook. I have a Pivot
Table (which is refreshed each time in the loop) using this data, and a
print routine that prints the data from all nine workbooks, the idea being
to automate printing a daily report from each location.
In the piece of code above, "Intercooler" is one of a hundred or so
items that show in the Pivot Table dropdown list. My question is :- Is it
possible to have "Intercooler" inserted in the code via a cell value in the
worksheet in a similar way to for example

Range([A1]).Copy

My aim is to use a Combobox in my workbook to select the category to
print so that I don't have to alter the code if a different category is
required,

Any advice much appreciated,

Regards,

Alan.

Windows XP Pro
Office XP Pro

Pivot Table
 

Not sure of your design but why not use the combo box already provided by the pivot. Anyway, if I understand your question, I'd do it like this on the combo change event:

Sub DropDown1.Change()
ActiveSheet.Shapes("Drop Down 1").Select

Sheet3.PivotTables("PivotTable2").PivotFields("Typ e").CurrentPage = _
selection.list(selection.listindex)
End Sub

"Alan" wrote:

Hi,

Sheet3.PivotTables("PivotTable2").PivotFields("Typ e").CurrentPage = _
"Intercooler"

I have a macro which links to nine other workbooks which are identical
in layout. The macro loops through all nine workbooks and enters data from
the same place in each workbook into Sheet1 in my workbook. I have a Pivot
Table (which is refreshed each time in the loop) using this data, and a
print routine that prints the data from all nine workbooks, the idea being
to automate printing a daily report from each location.
In the piece of code above, "Intercooler" is one of a hundred or so
items that show in the Pivot Table dropdown list. My question is :- Is it
possible to have "Intercooler" inserted in the code via a cell value in the
worksheet in a similar way to for example

Range([A1]).Copy

My aim is to use a Combobox in my workbook to select the category to
print so that I don't have to alter the code if a different category is
required,

Any advice much appreciated,

Regards,

Alan.

Windows XP Pro
Office XP Pro