VBA for Pivot Tables

[Bacon117]

Hope no one minds, I recreated this post with a better title, since I was getting no responses.

I need to quickly change which field is listed in the Values area of a pivot table. My current method is to drag the field out of the values area, drag the new field into the values area, right click it, and select average.

I would really like to write a <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> script to handle this for me based on a value in a cell (that has a drop down list populated from the header of my data). BUT, I cannot seem to find a way to determine WHICH field is currently in the values area before deleting it from the pivot table. All of the tutorials (and microsoft reference) show how to remove a field from a pivot chart, but only if you know which field is already there. I suppose I could remove everything, then add it all back, but I think that's a rabbit hold I'm not prepared for.

Any suggestions?

Thanks.

 

[sheetspread]

This will remove all the value fields, then replace with one of your choice:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Name	Group	Series1	Series2
2	Q	A	64	89
3	W	A	60	37			Sum of Series2	Column Labels
4	E	A	52	26			Row Labels	A	B	C	D	E	F	G
5	R	A	9	77			A				98
6	T	B	1	99			D				85
7	Y	C	89	10			E	26
8	U	C	39	44			F					3
9	I	C	23	63			G						4
10	O	C	70	11			H							93
11	P	D	85	41			I			63
12	A	D	76	98			J							30
13	S	D	89	13			K							82
14	D	D	63	85			L							34
15	F	E	58	3			O			11
16	G	F	35	4			P				41
17	H	G	90	93			Q	89
18	J	G	33	30			R	77
19	K	G	27	82			S				13
20	L	G	13	34			T		99
21	Z	G	21	33			U			44
22							W	37
23							Y			10
24							Z							33
Test

Sub Macro1()
Dim PT As PivotTable, PTField As PivotField
Set PT = Sheets("Test").PivotTables("PivotTable1")
With PT
    .ManualUpdate = True
    For Each PTField In .DataFields
        PTField.Orientation = xlHidden
    Next PTField
    .ManualUpdate = False
    .AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Series1"), "Sum of Series1", xlSum
End With
Set PT = Nothing
End Sub

Excel 2010
	G	H	I	J	K	L	M	N
3	Sum of Series1	Column Labels
4	Row Labels	A	B	C	D	E	F	G
5	A				76
6	D				63
7	E	52
8	F					58
9	G						35
10	H							90
11	I			23
12	J							33
13	K							27
14	L							13
15	O			70
16	P				85
17	Q	64
18	R	9
19	S				89
20	T		1
21	U			39
22	W	60
23	Y			89
24	Z							21
Test

(Series2 becomes Series1)

 

[Bacon117]

Hello, thank you for your reply. I appreciate it.

Unfortunately, this doesn't quite fix my problem. This will add the field to the pivot table, but does not specify which area the field goes to. The areas are "Report Filter", "Column Labels", "Row Labels", and "Values".

How do I specify that "Series1" is added to the "Values" area?

 

[sheetspread]

Series1 was added to Values and nowhere else, what is the code returning on your table?

 

[Bacon117]

Ok, I got it to work. It was actually causing an error, because I used the field name as the label name. So, thank you very much for that.

Is there anyway to specify that fields go into "Column Labels" or "Report Filters" instead of "Values"? Or do I have to manually setup the pivot Table first?

In other words, is there anyway to move Series1 to "Row Labels" or "Column Labels"?

 

[sheetspread]

Something like this?

Sub Macro1()
Dim PT As PivotTable, PTField As PivotField
Set PT = Sheets("Test").PivotTables("PivotTable1")
With PT
    .ManualUpdate = True
    For Each PTField In .DataFields
        PTField.Orientation = xlHidden
    Next PTField
    .ManualUpdate = False
    .AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Series1"), "Sum of Series1", xlSum
        .PivotFields("Sum of Series1").Orientation = xlRowField
End With
Set PT = Nothing
End Sub

 

[Bacon117]

You're my hero! That will work.

Thanks.