Pivot Table Macro

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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top