Smittybball40
New Member
- Joined
- Apr 28, 2019
- Messages
- 1
Hi!
I have created a pivot table that shows customer in column A, then the sales and sales difference in percent in the following columns by month. So column B & C are grouped under January with actual sales and percent difference from previous month (since there is no previous month, it is blank). Column D & E would be February and so on.
The data range is broken out by quarter, month, and year. So I have the ability to change the above described pivot table to switch out the month to quarter or year. I wrote a macro and assigned them to buttons with corresponding names that does this but as I click through the buttons for Month, Quarter, and Year it then un-groups my columns and then re-groups them by sales amount per month then percent difference per month.
What I need:
Jan Feb
Sales | Diff Sales | Diff
What Macro is doing:
Sales Diff
Jan | Feb Jan | Feb
I also set up the Pivot table Diff columns with Direction Arrow Icon sets in conditional formatting. The code is removing those everything I run it. I need to find a way to keep them in my document.
I would appreciate any help possible here. Thanks in advance! Here is the code I am using:
Sub Add_Column_Field()
'Remove all Column fields and add the Column field to the pivot table.
'The field is determined by the button text that calls the macro.
Dim pt As PivotTable
Dim pf As PivotField
Dim sField As String
'Set variables
Set pt = ActiveSheet.PivotTables(1)
sField = ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text
'Remove existing fields
For Each pf In pt.ColumnFields
If pf.Name <> "Values" Then
pf.Orientation = xlHidden
End If
Next pf
'Add field that button was clicked for
pt.PivotFields(sField).Orientation = xlColumnField
End Sub
I have created a pivot table that shows customer in column A, then the sales and sales difference in percent in the following columns by month. So column B & C are grouped under January with actual sales and percent difference from previous month (since there is no previous month, it is blank). Column D & E would be February and so on.
The data range is broken out by quarter, month, and year. So I have the ability to change the above described pivot table to switch out the month to quarter or year. I wrote a macro and assigned them to buttons with corresponding names that does this but as I click through the buttons for Month, Quarter, and Year it then un-groups my columns and then re-groups them by sales amount per month then percent difference per month.
What I need:
Jan Feb
Sales | Diff Sales | Diff
What Macro is doing:
Sales Diff
Jan | Feb Jan | Feb
I also set up the Pivot table Diff columns with Direction Arrow Icon sets in conditional formatting. The code is removing those everything I run it. I need to find a way to keep them in my document.
I would appreciate any help possible here. Thanks in advance! Here is the code I am using:
Sub Add_Column_Field()
'Remove all Column fields and add the Column field to the pivot table.
'The field is determined by the button text that calls the macro.
Dim pt As PivotTable
Dim pf As PivotField
Dim sField As String
'Set variables
Set pt = ActiveSheet.PivotTables(1)
sField = ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text
'Remove existing fields
For Each pf In pt.ColumnFields
If pf.Name <> "Values" Then
pf.Orientation = xlHidden
End If
Next pf
'Add field that button was clicked for
pt.PivotFields(sField).Orientation = xlColumnField
End Sub