The VBA Guide To Pivot Tables

User Manual:

Open the PDF directly: View PDF .
Page Count: 22

HOME ABOUT BLOG TUTORIALS ADD-INSCODE VAULT

September 27, 2014 Chris Newman

All About The Pivot Tables!

Pivot Tables and VBA can be a little tricky initially. Hopefully this

guide will serve as a good resource as you try to automate those

extremely powerful Pivot Tables in your Excel spreadsheets.

Enjoy!

Create A Pivot Table

Sub CreatePivotTable()

'PURPOSE: Creates a brand new Pivot table on a new worksheet

from data in the ActiveSheet

Free Webinars Going On Now!

My Favorite Add-ins & Arcles!

'Source: www.TheSpreadsheetGuru.com 
Dim sht As Worksheet 
Dim pvtCache As PivotCache 
Dim pvt As PivotTable 
Dim StartPvt As String 
Dim SrcData As String 
'Determine the data range you want to pivot 
 SrcData = ActiveSheet.Name & "!" &
Range("A1:R100").Address(ReferenceStyle:=xlR1C1) 
'Create a new worksheet 
 Set sht = Sheets.Add 
'Where do you want Pivot Table to start? 
 StartPvt = sht.Name & "!" &
sht.Range("A3").Address(ReferenceStyle:=xlR1C1) 
'Create Pivot Cache from Source Data 
 Set pvtCache = ActiveWorkbook.PivotCaches.Create( _ 
 SourceType:=xlDatabase, _ 
 SourceData:=SrcData) 
'Create Pivot table from Pivot Cache 
 Set pvt = pvtCache.CreatePivotTable( _ 
 TableDestination:=StartPvt, _ 
 TableName:="PivotTable1") 
End Sub
Delete A Specic Pivot Table
Sub DeletePivotTable() 
'PURPOSE: How to delete a specifc Pivot Table 
'SOURCE: www.TheSpreadsheetGuru.com 
'Delete Pivot Table By Name 
 ActiveSheet.PivotTables("PivotTable1").TableRange2.Clear 
End Sub
Delete All Pivot Tables
Sub DeleteAllPivotTables() 
'PURPOSE: Delete all Pivot Tables in your Workbook 
'SOURCE: www.TheSpreadsheetGuru.com 
Dim sht As Worksheet 
Dim pvt As PivotTable 
Latest VBA Code Vault Posts
Prevent Excel From
Automatically Formatting
Data As Dates When
Pasted
2 DAYS AGO
Generate A String Of
Random Characters With
VBA Code
2 MONTHS AGO
VBA Code To Convert
Column Number to Letter

'Loop Through Each Pivot Table In Currently Viewed Workbook

For Each sht In ActiveWorkbook.Worksheets

For Each pvt In sht.PivotTables

pvt.TableRange2.Clear

Next pvt

Next sht

End Sub

Add Pivot Fields

Sub Adding_PivotFields()

'PURPOSE: Show how to add various Pivot Fields to Pivot

Table

'SOURCE: www.TheSpreadsheetGuru.com

Dim pvt As PivotTable

Set pvt = ActiveSheet.PivotTables("PivotTable1")

'Add item to the Report Filter

pvt.PivotFields("Year").Orientation = xlPageField

'Add item to the Column Labels

pvt.PivotFields("Month").Orientation = xlColumnField

'Add item to the Row Labels

pvt.PivotFields("Account").Orientation = xlRowField

'Position Item in list

pvt.PivotFields("Year").Position = 1

'Format Pivot Field

pvt.PivotFields("Year").NumberFormat = "#,##0"

'Turn on Automatic updates/calculations --like

screenupdating to speed up code

pvt.ManualUpdate = False

End Sub

Add Calculated Pivot Fields

Sub AddCalculatedField()

'PURPOSE: Add a calculated field to a pivot table

'SOURCE: www.TheSpreadsheetGuru.com

Dim pvt As PivotTable

Dim pf As PivotField

'Set Variable to Desired Pivot Table

Or Letter To Number

3 MONTHS AGO

Free Webinars

Most Popular Blog Posts

Easy Step-By-Step

Instructions To Create Your

First Excel Ribbon Add-in

2 YEARS AGO

The VBA Guide To Excel

Pivot Tables

3 YEARS AGO

5 Different Ways to Find

The Last Row or Last

Column Using VBA

3 YEARS AGO

Copy & Paste Multiple Excel

Ranges To Separate

PowerPoint Slides With

VBA

3 YEARS AGO

Follow @ChrisMacro

Set pvt = ActiveSheet.PivotTables("PivotTable1")

'Set Variable Equal to Desired Calculated Pivot Field

For Each pf In pvt.PivotFields

If pf.SourceName = "Inflation" Then Exit For

'Add Calculated Field to Pivot Table

pvt.AddDataField pf

End Sub

Add A Values Field

Sub AddValuesField()

'PURPOSE: Add A Values Field to a Pivot Table

'SOURCE: www.TheSpreadsheetGuru.com

Dim pvt As PivotTable

Dim pf As String

Dim pf_Name As String

pf = "Salaries"

pf_Name = "Sum of Salaries"

Set pvt = ActiveSheet.PivotTables("PivotTable1")

pvt.AddDataField pvt.PivotFields("Salaries"), pf_Name, xlSum

End Sub

Remove Pivot Fields

Sub RemovePivotField()

'PURPOSE: Remove a field from a Pivot Table

'SOURCE: www.TheSpreadsheetGuru.com

'Removing Filter, Columns, Rows

ActiveSheet.PivotTables("PivotTable1").PivotFields("Year").Orientation

= xlHidden

'Removing Values

ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of

Salaries").Orientation = xlHidden

End Sub

Be the first of your friends to like this

The Spreadsh…

1.9K likes

Like Page

Remove Calculated Pivot Fields

Sub RemoveCalculatedField()

'PURPOSE: Remove a calculated field from a pivot table

'SOURCE: www.TheSpreadsheetGuru.com

Dim pvt As PivotTable

Dim pf As PivotField

Dim pi As PivotItem

'Set Variable to Desired Pivot Table

Set pvt = ActiveSheet.PivotTables("PivotTable1")

'Set Variable Equal to Desired Calculated Data Field

For Each pf In pvt.DataFields

If pf.SourceName = "Inflation" Then Exit For

'Hide/Remove the Calculated Field

pf.DataRange.Cells(1, 1).PivotItem.Visible = False

End Sub

Report Filter On A Single Item

Sub ReportFiltering_Single()

'PURPOSE: Filter on a single item with the Report Filter

field

'SOURCE: www.TheSpreadsheetGuru.com

Dim pf As PivotField

Set pf =

ActiveSheet.PivotTables("PivotTable2").PivotFields("Fiscal_Year")

'Clear Out Any Previous Filtering

pf.ClearAllFilters

'Filter on 2014 items

pf.CurrentPage = "2014"

End Sub

Report Filter On Multiple Items

Sub ReportFiltering_Multiple()

'PURPOSE: Filter on multiple items with the Report Filter

field

'SOURCE: www.TheSpreadsheetGuru.com

Dim pf As PivotField

Set pf =

ActiveSheet.PivotTables("PivotTable2").PivotFields("Variance_Level_1")

'Clear Out Any Previous Filtering

pf.ClearAllFilters

'Enable filtering on multiple items

pf.EnableMultiplePageItems = True

'Must turn off items you do not want showing

pf.PivotItems("Jan").Visible = False

pf.PivotItems("Feb").Visible = False

pf.PivotItems("Mar").Visible = False

End Sub

Clear Report Filter

Sub ClearReportFiltering()

'PURPOSE: How to clear the Report Filter field

'SOURCE: www.TheSpreadsheetGuru.com

Dim pf As PivotField

Set pf =

ActiveSheet.PivotTables("PivotTable2").PivotFields("Fiscal_Year")

'Option 1: Clear Out Any Previous Filtering

pf.ClearAllFilters

'Option 2: Show All (remove filtering)

pf.CurrentPage = "(All)"

End Sub

Refresh Pivot Table(s)

Sub RefreshingPivotTables()

'PURPOSE: Shows various ways to refresh Pivot Table Data

'SOURCE: www.TheSpreadsheetGuru.com

'Refresh A Single Pivot Table

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

'Refresh All Pivot Tables

ActiveWorkbook.RefreshAll

End Sub

Change Pivot Table Data Source Range

Sub ChangePivotDataSourceRange()

'PURPOSE: Change the range a Pivot Table pulls from

'SOURCE: www.TheSpreadsheetGuru.com

Dim sht As Worksheet

Dim SrcData As String

Dim pvtCache As PivotCache

'Determine the data range you want to pivot

Set sht = ThisWorkbook.Worksheets("Sheet1")

SrcData = sht.Name & "!" &

Range("A1:R100").Address(ReferenceStyle:=xlR1C1)

'Create New Pivot Cache from Source Data

Set pvtCache = ActiveWorkbook.PivotCaches.Create( _

SourceType:=xlDatabase, _

SourceData:=SrcData)

'Change which Pivot Cache the Pivot Table is referring to

ActiveSheet.PivotTables("PivotTable1").ChangePivotCache

(pvtCache)

End Sub

Grand Totals

Sub PivotGrandTotals()

'PURPOSE: Show setup for various Pivot Table Grand Total

options

'SOURCE: www.TheSpreadsheetGuru.com

Dim pvt As PivotTable

Set pvt = ActiveSheet.PivotTables("PivotTable1")

'Off for Rows and Columns

pvt.ColumnGrand = False

pvt.RowGrand = False

'On for Rows and Columns

pvt.ColumnGrand = True

pvt.RowGrand = True

'On for Rows only

pvt.ColumnGrand = False

pvt.RowGrand = True

'On for Columns Only

pvt.ColumnGrand = True

pvt.RowGrand = False

End Sub

Report Layout

Sub PivotReportLayout()

'PURPOSE: Show setup for various Pivot Table Report Layout

options

'SOURCE: www.TheSpreadsheetGuru.com

Dim pvt As PivotTable

Set pvt = ActiveSheet.PivotTables("PivotTable1")

'Show in Compact Form

pvt.RowAxisLayout xlCompactRow

'Show in Outline Form

pvt.RowAxisLayout xlOutlineRow

'Show in Tabular Form

pvt.RowAxisLayout xlTabularRow

End Sub

Formatting A Pivot Table's Data

Sub PivotTable_DataFormatting()

'PURPOSE: Various ways to format a Pivot Table's data

'SOURCE: www.TheSpreadsheetGuru.com

Dim pvt As PivotTable

Set pvt = ActiveSheet.PivotTables("PivotTable1")

'Change Data's Number Format

pvt.DataBodyRange.NumberFormat = "#,##0;(#,##0)"

'Change Data's Fill Color

pvt.DataBodyRange.Interior.Color = RGB(0, 0, 0)

'Change Data's Font Type

pvt.DataBodyRange.Font.FontStyle = "Arial"

End Sub

Formatting A Pivot Field's Data

Sub PivotField_DataFormatting()

'PURPOSE: Various ways to format a Pivot Field's data

'SOURCE: www.TheSpreadsheetGuru.com

Dim pf As PivotField

Set pf =

ActiveSheet.PivotTables("PivotTable1").PivotFields("Months")

'Change Data's Number Format

pf.DataRange.NumberFormat = "#,##0;(#,##0)"

'Change Data's Fill Color

pf.DataRange.Interior.Color = RGB(219, 229, 241)

'Change Data's Font Type

pf.DataRange.Font.FontStyle = "Arial"

End Sub

Expand/Collapse Entire Field Detail

Sub PivotField_ExpandCollapse()

'PURPOSE: Shows how to Expand or Collapse the detail of a

Pivot Field

'SOURCE: www.TheSpreadsheetGuru.com

Dim pf As PivotField

Set pf =

ActiveSheet.PivotTables("PivotTable1").PivotFields("Month")

'Collapse Pivot Field

pf.ShowDetail = False

'Expand Pivot Field

pf.ShowDetail = True

End Sub

More Great Posts Dealing with Pivot Table

VBA

Quickly Change Pivot Table Field Calculation From

Count To Sum

Dynamically Change A Pivot Table's Data Source

Range

Dynamically Change Every Pivot Table Data Source

Range Inside A Workbook

5 Different Ways To Find The Last Row Or Last

Column Using VBA

Filtering Pivots Based On External Ranges

(DailyDoseOfExcel)

Inversely Filter A Pivot Based On An External

Range (DailyDoseOfExcel)

Any Other Functionalities You Would Like

To See?

I believe I was able to cover all the main pivot table VBA

functionalities in this article, but there is so much you can do

with pivot tables! Leave a comment below if you would like to

see something else covered in this guide.

Check out my brand new Excel add-in that allows you to automate creating your monthly

PowerPoint slides!

How Do I Modify This To Fit My Specic

Needs?

Chances are this post did not give you the exact answer you

were looking for. We all have different situations and it's

COPY & PASTE MULTIPLE EXCE

SETTING THE PROPER PASSWOR

Comments Community Login



⤤Sort by Best

Name

Join the discussion…

•Reply •

Kiwi overseas • 3 years ago

This is fabulous, This is exactly the information I'm needing

to build reports for a project at work. I appreciate the clear

commenting and your straightforward stick-to-the-

fundamentals examples. These are enough to get a

beginner, like me, going.

△

▽

Recommend

7

Share ›

impossible to account for every particular need one might have.

That's why I want to share with you: My Guide to Getting the

Solution to your Problems FAST! In this article, I explain the

best strategies I have come up with over the years to getting

quick answers to complex problems in Excel, PowerPoint,

VBA, you name it!

I highly recommend that you check this guide out before

asking me or anyone else in the comments section to solve your

specific problem. I can guarantee 9 times out of 10, one of my

strategies will get you the answer(s) you are needing faster than

it will take me to get back to you with a possible solution. I try

my best to help everyone out, but sometimes I don't have time

to fit everyone's questions in (there never seem to be quite

enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you

heading in the right direction!

Chris "Macro" Newman :)

 86 Comments

VBA, Excel, Guides

Pivot Tables, Pivot Fields, E2P

11 Likes Share

•Reply •

Chris • 3 years ago

Mod > Kiwi overseas

Thank you for the feedback Kiwi!

△

▽

•Reply •

Marcel Haller • a year ago

A realy straight forward tutorial ! Many thanks, this is very

usefull and perfectly explained !

△

▽

•Reply •

Chris • a year ago

Mod > Marcel Haller

Thanks for the feedback Marcel and I'm so glad to

hear this post helped you!

△

▽

•Reply •

dan • 3 years ago

Thank you for the guide, very useful. When filtering multiple

items, is there a way to select items that you do want to

see, rather than turning off items that you don't want to see?

I have hundreds of items I don't want to see, and these

items change each time, and only 6 items I do want to see.

△

▽

•Reply •

Chris • 3 years ago

Mod > dan

I'm currently writing an article about this. I will let you

know when it is posted.

△

▽

•Reply •

Chris • 3 years ago

Mod > Chris

Just posted an article on VBA and Advanced

Filters. This should help you add the

capability of filtering specific criteria.

http://bit.ly/19q2iBz

△

▽

Alex • a year ago> Chris

Watch this

Sub Macro1()

Dim Tbl1 As PivotTable

Dim Itm1 As PivotItem

Set Tbl1 =

Worksheets("Hoja1").PivotTables("Tabl

Tbl1.PivotFields("Campo1").ClearAllFil

Tbl1.PivotFields("Campo1").EnableMu

= True

For Each Itm1 In

Tbl1.PivotFields("Campo1").PivotItems

Share ›

•Reply •

If Itm1.Name = "Texto1" Then

'Nothing

ElseIf Itm1.Name = "Texto2" Then

'Nothing

...

...(Repeat code)

...

Else

Itm1.Visible = False

End If

End Sub

△

▽

•Reply •

Lalit Bhandary • 2 months ago

How do I use multiple field filter? I saw multiple item but not

multple fields, can you please demonstrate by coding.

Thanks

△

▽

•Reply •

Jorge Bejarano Hermoza • 4 months ago

Good afternoon, please where can I get the file with the

data in order to apply the solutions you give for this Excel

Pivots Tables topic. Please tell me where I can download it.

Thank you very much for your understanding

Greetings from Peru Jorge

Note: This is a translation with Google Translate

△

▽

Todd Choi • 5 months ago

Everything looks great, but for some odd reason there are

moments when the macro does not work. I would use it

perfectly fine one day and then the next there would be an

error. please refer to the following code:

Sub ShortagePivotTable()

Dim sht As Worksheet

Dim pvtCache As PivotCache

Dim pvt As PivotTable

Dim StartPvt As String

Dim SrcData As String

SrcData = ActiveSheet.Name & "!" &

ActiveSheet.UsedRange.Address(ReferenceStyle:=xlR1C1)

Set sht = Sheets.Add

StartPvt = sht.Name & "!" &

Share ›

•Reply •

△

▽

•Reply •

David S. Savioz • 6 months ago

Thanks for the code!

However, I still have an issue when I tried to update the row

and column field in my Pivot table based on the value of two

different cells. It works fine for the main filter which is the

Region and then block on the categ part (pfcat.CurrentPage

= categ)

Does anyone have an idea why?

-------

Private Sub Worksheet_Change(ByVal Target As Range)

Dim pvt As PivotTable

Dim pf As PivotField

Dim pfcat As PivotField

Dim pfcomp As PivotField

Dim region As String

Dim categ As String

Dim comp As String

△

▽

Walt Herman • 10 months ago

Nice work, thanks so much for sharing. I have an

"Worksheet Activate" macro which is failing with an invalid

procedure call error. I am using a BITool which creates a

"copy" of the template worksheet, the source data comes

from the BITool but in the process the pivot table source

range becomes corrupted with a reference to what I can

only describe as an "interim" filename and sheet and cell

reference hence the need for my always having to edit the

source reference. Do you have a moment to share your

perspective? In any event, thanks so much for sharing all of

this great info on VBA for PivotTables...

Private Sub Worksheet_Activate()

Application.Goto Reference:="Sheet2!R1C1"

ActiveSheet.PivotTables("PivotTable2").ChangePivotCache

ActiveWorkbook. _

PivotCaches.Create(SourceType:=xlDatabase,

SourceData:="Sheet1!DATABASE" _

, Version:=xlPivotTableVersion15)

ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh

End Sub

Share ›

•Reply •

△

▽

•Reply •

Lori • 10 months ago

Marcel,

Perhaps I am missing something, but this link looks like a

data refresh and not a change in my pivot tables' Data

Source Range, or the range from which the pivot table pulls

all the data I use to display my data.

△

▽

•Reply •

Marcel Haller • a year ago

Hi Lori, ok I understood wrong. To refresh 1 single table with

VBA you can use a command like this:

Worksheets("tabel").PivotTables("Tabella_pivot_kosten").Pivo

The side below explain pretty well ho to do this for all tables

in one time:

http://www.globaliconnect.c...

Hope this will help you

△

▽

•Reply •

Lori • a year ago

> Marcel Haller

Hi Marcel. Thank you for your response. I appreciate

it. Allow me to clarify. My workbook is filled with 100+

pivot tables located on multiple worksheets. All of

these pivot tables share the SAME data source

range: "G2!$C$3:$CLJ$7". I want the ability to

change that range in ALL pivot tables whenever

there is a need (for example, to: G1!$C$6:$CLK$15).

Ideally, I want to click a button that will ask me to

enter a new "Data Source Range" and once done,

the VBA code will change the range for ALL of the

pivot tables within my workbook so that, instead of

just looking at FY14 data, my pivot charts would

present FY 17 data (or whatever range I request).

Chris appears to have accomplished this, more or

less, for ONE worksheet (see: Change Pivot Table

Data Source Range within this article). However, I

need it to do it for ALL of my pivot tables in my

workbook.

△

▽

Marcel Haller • a year ago

> Lori

Ok, Lori, in the end it's not so difficult. On the

sheet with your data put this code and every

table will be update after any update in your

data:

Private Sub Worksheet_Change(ByVal Target

As Range)

'Refresh All Pivot Tables

Share ›

•Reply •

ThisWorkbook.RefreshAll

End Sub

Hope this will help you,

Marcel

△

▽

•Reply •

Marcel Haller • a year ago

> Lori

Hi Lori, that's clear and I understand what

you mean. This is not to difficult with some

vba code. I will work it out for you this

weekend.

△

▽

•Reply •

Marcel Haller • a year ago

Hi Lori, I can suggest you this video, which answer your

question and is also very well explained:

▶

△

▽

•Reply •

Lori • a year ago

> Marcel Haller

Unfortunately, that page is blocked on my work

computer. :-(

Is this the following the correct address?

https://cdn.embedly.com

If so, any other suggestions?

△

▽

Lori • a year ago

> Lori

Marcel, for some reason this video finally

opened. I wasn't questioning the creation of

pivot charts. My question is how to convert

the VBA code he Chris has listed to

automatically work for all of the pivot tables in

Share ›

•Reply •

y p

my workbook. I also want it to pop up a

question for the range, so all I have to do is

input that information and it knows that data

source range to use. I do not have any formal

education with VBA, so I only know enough

to get a few things accomplished.

△

▽

•Reply •

Lori • a year ago

Re: Change Pivot Table Data Source Range

Hi Chris,

I have a workbook that is filled with Pivot Tables. All of those

tables use the same data source range. How would I set

this up so that I can have all of my tables updated at the

same time. I haven't had a chance to play with what you

have posted so far, but I'd like something where I can just

list the range and push a button.

Please advise,

Lori

△

▽

•Reply •

Gabee • a year ago

I have the filter set to only show 1 item out of thousands to

calculate something. I need to be able to change the filter

using a Macro to go through each item. How can I do that?

△

▽

•Reply •

pramod dsrb • a year ago

This guide is Awesome!! I'm wondering if it is possible to

refer to the Pivot Fields in the Report Filter. What I'm asking

is, to refer to the Pivot fields in Row Lable, we use:

pt.RowFields(1).PivotItems. Similarly do we have anything

to refer to the fields in report filter?

△

▽

•Reply •

Michael New • a year ago

Thank you for the tutorials. I wonder if you can help with

setting the background color of a pivot field? I have

successfully added a field and moved it to a different

position. However, the header value should have the same

backcolor as my other fields, but it does not. Any ideas? My

Code:

ThisWorkbook.Worksheets(sheetPivot).PivotTables(1).AddFi

RowFields:="Spacing", AddToTable:="True"

With

ThisWorkbook.Worksheets(sheetPivot).PivotTables(1).PivotF

.Orientation = xlRowField

.Position = 6

End With

△

▽

Share ›

•Reply •

Deepthi Kunhappan • a year ago

Hey Chris! Thank you so much for this.. I was particularly

looking for the add a value field. could you help me with my

query I have and I been struggling with this for quite some

time. I am not a VBA professional and have been learning

all that I can online.

Below is a table that I would like to create using VBA, I have

managed to get a very basic report using VBA but cant

seem to get this one. As you can the the the second row

fields there are a lot of value parameters. Can you please

help me in figuring out how I can get these Value fields into

a second column rather than in one row so that I can filter

these? I hope this is possible.

kindly advise

⛺

△

▽

•Reply •

Mario • a year ago

Hi, thanks for all this information, but. i have a question or

you may explain how to do it. i want to filter information on

the pivot table using as a reference the information on an

specific cell so when i run the macro the pivot table will be

updated and will use the specific cell to filter the data.

if you have time to put an example of it i will really apreciate

it thanks :D

△

▽

•Reply •

Greg • a year ago

Question about Report Filter on a single item. If I have a

variable that references a cell on another worksheet versus

"2014" what format does it need to be in to work? I'm

struggling with getting this to the correct format for it to

process. I've tried Variant, String, and Integer but none

seem to be working for me.

△

▽

•Reply •

clark kevin Songcaya • a year ago

Thanks this is very helpful. You're great. I want to know if

you also have for pivot data filter? Thanks in advance. :-)

△

▽

Share ›

•Reply •

Bryan Wacker • 2 years ago

Great article. I do have a couple questions. Is there a way to

maintain settings that I changed for a value when I remove

from and then add back into a pivot table? For instance, I

have Sales $ as a metric which defaults to Count/General

number format. I changed it to Sum/Currency number

format however when I pull it out of the pivot table and then

put it back in, it reverts back to Count/General number

format. Anyway to get this to stop?

I also have time periods across the top of my pivot table and

I have my rows sorted on Sales $ within a specific time

period. When I remove that time period my sorting goes

away (which makes sense), but is there anyway to have it

sort on Sales $ within another time period if I remove the

original sorted time period?

△

▽

•Reply •

EH Chew • 2 years ago

Cool stuff... I love it :) I found some of them very useful.

Thanks

△

▽

•Reply •

Chris • 2 years ago

Mod > EH Chew

Glad you found it useful!

△

▽

•Reply •

Third From The Sun • 2 years ago

Is there a programmatic way to select the criteria of a

PivotTable?

I have a series of Pivots in separate worksheets all

referencing the same data in another worksheet. The

associated Pivot charts have been copied into a single

multi-panel worksheet for printing as a single page. I'd like

to select the criteria in one pivot (or elsewhere) and have all

the pivots update to the same criteria, so the multi-panel

plots are all based on the same slice through the data (e.g.,

for a selected year, city, department).

thanks for any tips!

△

▽

Deep Dave • 2 years ago

For the Topic Report Filter On A Single Item

It does not work when the Item to be filtered is a Date.

Can you guide how to filter dates?

For Example this code -

Share ›

•Reply •

Sub PivotDates()

Dim PT As PivotTable, WS As Worksheet, PI As PivotItem

For Each WS In ThisWorkbook.Worksheets

For Each PT In WS.PivotTables

With PT

.ClearAllFilters

.PivotFields("Date").PivotItems = Format(Date, "d-mmm-

yy")

End With

Next PT

Next WS

End Sub

△

▽

•Reply •

Dr. Demento • 2 years ago

Chris,

Solid work, as always.

As usual, I'm trying to combine pieces of your code but

botching it. Could you help me to merge the CreatePivot

using LastRow and/or LastCol instead of hardcoding the

range in? I'm running thru multiple worksheets where the

data is structured identically (columns are same in each

ws), but have differing number of rows.

Thanks much, sir!

Nevermind - I found it on your Dynamic Auto-Adjusting VBA

Range post. :-p

△

▽

•Reply •

Karen Franklin • 2 years ago

I'm trying to get VBA code to compare 2 pivot tables in the

same sheet and highlight the differences between pivot

table 2 (after) and pivot table 1 (before). The ranges could

change meaning there could be more values in the after

table than in the before table.

△

▽

•Reply •

jesus5150 • 2 years ago

I need to have one pivot table filter change several other

pivot table filters and they are are from different data

sources. Is that possible?

Thanks

△

▽

Arputharaj Vincent • 2 years ago

Hi Chris,

Hope you are doing Good.

Share ›

•Reply •

Do you have any sample data (or) already created excel

macro file for the above scenario.

Could you please share with me to my email id -

arputhamv@gmail.com

Thanks and Regards,

Arputharaj.V

△

▽

•Reply •

Cynthia Roxana Panez Velazco • 2 years ago

Hi! I need to create multiple pivot tables from the same

source data, is it possible?

Thnks! :)

△

▽

•Reply •

Chris

• 2 years ago

Mod > Cynthia Roxana Panez Velazco

Yes, you will just need to use the same PivotCache

when creating your other pivot table

△

▽

•Reply •

kadr leyn • 2 years ago

Thanks for tutorial.

I made the template about Excel Pivot Table.You can create

an Excel Pivot Table report using VBA in this template.Also

you can copy pivot table into Ms Word.All operations with

one button.

Template link : http://adf.ly/1GN06W

⛺

△

▽

•Reply •

John W • 2 years ago

What a great guide! How do i use a value in a combo box

field, to set the filter? Ultimately I'd like to use a List Box to

set multiple values but baby steps...

△

▽

Chris • 2 years ago

Mod > John W

You will want to setup an ActiveX combo box and

then when someone makes a selection it would

Share ›

Aliates(Coming Soon!)| About | Tools| Example Files
Guru Solutions, LLC | © 2015-2017 | ALL RIGHTS RESERVED 
Excel, PowerPoint, Word, & the rest of the Oce Suite are registered trademarks of the Microsoft Corporation 
This site is not aliated with Microsoft Corporation.

The VBA Guide To Pivot Tables

Navigation menu

Versions of this User Manual:

Views

Navigation