Pivot Tables: Page field in a Combobox in another worksheet

[hsalortb]

Hi,

How can I put (or control) a PAGE FIELD of a Pivot Table in another worksheet in the same MS Excel workbook.

Thanks.

HS

 

[Marshalls]

Hope this is what you mean,

this is the pivot table

xmr shift.xls
	A	B	C	D
3	AllWorkcentres	StIvesPress2
4
5	UnplannedDownTime
6	Date	ShiftDesc	Total
7	04/01/2005	Days12Hour	273	1
8		Nights12Hour	97	1
9	05/01/2005	Days12Hour	121	2
10		Nights12Hour	42	2
11	06/01/2005	Days6-3	46	3
12	10/01/2005	Days12Hour	252	4
13		Nights12Hour	690	4
14	11/01/2005	Days12Hour	627	5
15		Nights12Hour	196	5
16	12/01/2005	Days12Hour	268	6
17		Nights12Hour	112	6
18	13/01/2005	Days6-3	42	7
19		Nights12Hour	23	7
20	17/01/2005	Days12Hour	219	8
21		Nights12Hour	308	8
22	18/01/2005	Days12Hour	395	9
23		Nights12Hour	242	9
24	19/01/2005	Days12Hour	237	10
25		Nights12Hour	97	10
26	20/01/2005	Days12Hour	50	11
27		Nights12Hour	79	11
28	24/01/2005	Days12Hour	298	12
29		Nights12Hour	343	12
PivotTable

Also i did a macro changing the field i want to change from one machine to the other and this is some of the code i got

Sheets("PivotTable").Select
ActiveSheet.PivotTables("Downtime").PivotFields("[All Workcentres]"). _
CurrentPageName = _
"[All Workcentres].[All Workcentres].[Landscape & Walling].[Marshalls L&W].[Southern].[RV - St Ives].[St Ives Flag and kerb dept.].[St Ives Press 1]"
Sheets("Sheet1").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

Also the combobox on the pivot table is grouped does this make a difference if so how can i replicate that?

 

[Marshalls]

Dont know what has happend to the pivot tables data probably because it hasnt got the network.

 

[Andrew Poulsom]

I'm a bit stuck, because I am not familiar with the square bracket and dot syntax in:

ActiveSheet.PivotTables("Downtime").PivotFields("[All Workcentres]"). _ 
CurrentPageName = _ 
"[All Workcentres].[All Workcentres].[Landscape & Walling].[Marshalls L&W].[Southern].[RV - St Ives].[St Ives Flag and kerb dept.].[St Ives Press 1]"

My code was for a simple ungrouped Page Field.

If you run this, what do you get?

Sub Test()
   Dim PItem As PivotItem
   Dim Msg As String
   For Each PItem In ActiveSheet.PivotTables("Downtime").PivotFields("[All Workcentres]").PivotItems
      Msg = Msg & PItem.Name & vbCrLf
   Next PItem
   MsgBox Msg
End Sub

 

[hsalortb]

Hi,

I did make the copy of the fields from a PIVOT TABLE to a new worksheet, and I could add new fields, incoporating new formulas, and, also, with the Andrew help (PIVOT FIELDS code in COMBOBOX) I can control the data in this new worksheet, making a selection through the COMBOBOXES (I'm using more of one) that make filtering over the original data at the PIVOT TABLE.

The idea is that new worksheet is like a "mirror" of the PIVOT TABLE (located in the same workbook but in another worksheet). In this "mirror" I can manage the data with more flexibility than in the PIVOT TABLE, but I maintain the connection with the external database and also the data processing features of the PIVOT TABLE.

However, I don't know how to transfer from the PIVOT TABLE to the new worksheet, some features of the PIVOT TABLE, like row formats (font, lines) and pake breaks after selected subtotals.

Could you please help me with this issue ?


Thanks in advance.


HS


[/img]

 

[Marshalls]

Hi Andrew if i run that code i get this error

Run-Time error '1004':

Application-defined or object-defined error

 

[Andrew Poulsom]

On what line do you get that error? Is the PivotTable on the ActiveSheet?

It's going to be difficult for me to test any code I write because I don't have an OLAP data source.

 

[Marshalls]

It doesnt give a line and no the pivot tbale is not on the active sheet.

 

[Marshalls]

Do i need to use another control type to do the combobox with the + and -'s with the tree affect?

 

[Andrew Poulsom]

It doesnt give a line and no the pivot tbale is not on the active sheet.

Make sure the sheet with the PivotTable is active before running the code.

 

[Marshalls]

Right sorry i didnt see that, anyways i ran the code and get a blank message box with the ok button.