Pivot Tables in Excel VBA – Explained with Examples!

Hi Valli,
you are so good..
would you be able to demonstrate pivot tables with conditional formatting in vba .
say I would like to format all values in . PivotFields(“Percent of “) for anything between 10 and 25 in red , 20 and 50 in blue and 50 to 75 in yellow.
How would you do this?

Hi Lisa,

I need a quick help. I have a complicated Pivot table, with main status showing on dates for 5 different columns. The date of each column had to be compared with todays date and status of the template say example 1, then as below

as of today(02.05.15) if I look at the column 1 date(01.14.15) and status = 1, I have to mark the cell as RED color.

Please help.

Thanks
Durga.

Hi,

I wanted to creat pvot table, Based on pivot field creteria tab.

for example.In sheet 2 i have pvot feild list Field1–Label1(Geography),Label2-(Industry),Label3-(Sector),Data ColumnsLabel1- (Cost),Label2(Value).In Sheet1 Data which is not constant.The size of the data keep on changing.

How would you do this.

Thanks
Faheem

Hello

I have coded to for pivot table I am getting correct table still I want some changes in that

1. same macro using 2nd time than should delet last pivot table
2. I want chnges in pivot table heading color and cell color
3. I need border for pivot table.
4. I want to change “grangd total” to “total work Completed”.
5. I want add comment below pivote table as work completed before 5pm.

Now coded like these

Sub sbCreatePivot()
‘Declaration
Dim ws As Worksheet
Dim pc As PivotCache
Dim pt As PivotTable

‘If “Pivot” worksheet already exists, delete it

On Error Resume Next
Application.DisplayAlerts = False
Sheets(“Pivot”).Delete
On Error GoTo 0
Application.DisplayAlerts = True

‘Adding new worksheet
Set ws = Worksheets.Add

‘Creating Pivot cache
Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, “Sheet1!R1C1:R13C8”)

‘Creating Pivot table
Set pt = pc.CreatePivotTable(ws.Range(“B3”))

‘Setting Fields
With pt
‘set row field
With .PivotFields(“Type”)
.Orientation = xlRowField
.Position = 1

End With

‘set data field
.AddDataField .PivotFields(“work count”), ” work count”, xlSum
End With
End Sub

Date from Type IteM# work App weight serial
12/23/2015 x A 2312 1 A 233 31123
12/23/2015 y B 3 2 b 213 123
12/23/2015 z C 21 3 c 313 3123
12/23/2015 n D 321 1 d 13 313
12/23/2015 m E1 12 2 a 13 3
12/23/2015 f B 1231 3 s 13 12
12/23/2015 r C 213 4 s 13 31
12/23/2015 n A 123 2 d 31 3
12/23/2015 e D 13 4 s 2 12
12/23/2015 s F 123 20 d 123 3
12/23/2015 x G 31 2 a 3 12

above table I need colums are Type and work.

Now I am geeting below shown

Row Labels Sum of Work
A 3
B 5
C 7
D 5
E1 2
F 20
G 2
(blank)
Grand Total 44

can you you help me out to make required changes

Hai sir,
i need your help, can u help me i am explain here what i need, actually i am doing a business so i need monthly outstanding report with easily i am using excel for entering details of sales so how can i get each party report i need party name qty of sale totl amount of each date like pls help

Hi there,

I am trying to use vba code to select a single field from my pivot then go back to the pivot to select multiple fields from the filter.

Any ideas?

Thanks
Krishna

Hi guys. Need a quick help.

For my monthly reporting , how can I make the pivot to add a new month by itself , as and when a new month raw data is added??

Thanks
Krishnan

….correction, I used the code in Excel 2013 (not 2010).

Hai Valli,

I am new to vba. I have some requirement based on manipulating data. I have spent time on googling to complete the things, but i can’t understand by their way of approach. I recently found your site, you are providing good things to learn. I need a sample excel macro file that satisfy the below listed operations,

1) Copy multiple sheets entire data to one workbook
2) Add some coulmns
3) Make some multiple column filter with multiple criteria
4) Fill cell based on filtered results to new column
5) Make VLOOKUP with opened another sheet data
5) Finally form some PIVOT TABLE .

Please guide me how to do the above.

Thanks in ADVANCE…..