The VBA Guide To Pivot Tables
User Manual:
Open the PDF directly: View PDF 
.
Page Count: 22
| Download | |
| Open PDF In Browser | View PDF |
CLICK HERE to sign up for my EXCLUSIVE Email Newsletter for more Tips,
Discounts, & Downloadable Content!
HOME
ABOUT
BLOG
TUTORIALS
CODE VAULT
ADD-INS
The VBA Guide To Excel
Pivot Tables
September 27, 2014 Chris Newman
Search
Free Webinars Going On Now!
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
My Favorite Add-ins & Ar cles!
Sub CreatePivotTable()
'PURPOSE: Creates a brand new Pivot table on a new worksheet
from data in the ActiveSheet
×
'Source: www.TheSpreadsheetGuru.com
Dim
Dim
Dim
Dim
Dim
sht As Worksheet
pvtCache As PivotCache
pvt As PivotTable
StartPvt As String
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
Latest VBA Code Vault Posts
Prevent Excel From
Automatically Formatting
Data As Dates When
Pasted
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
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
Or Letter To Number
End Sub
Free Webinars
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")
3 MONTHS AGO
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
'Add item to the Report Filter
pvt.PivotFields("Year").Orientation = xlPageField
3 YEARS AGO
'Add item to the Column Labels
pvt.PivotFields("Month").Orientation = xlColumnField
5 Different Ways to Find
The Last Row or Last
Column Using VBA
'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
3 YEARS AGO
Copy & Paste Multiple Excel
Ranges To Separate
PowerPoint Slides With
VBA
3 YEARS AGO
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
Follow @ChrisMacro
Set pvt = ActiveSheet.PivotTables("PivotTable1")
The Spreadsh…
'Set Variable Equal to Desired Calculated Pivot Field
For Each pf In pvt.PivotFields
If pf.SourceName = "Inflation" Then Exit For
Next
1.9K likes
'Add Calculated Field to Pivot Table
pvt.AddDataField pf
Like Page
End Sub
Be the first of your friends to like this
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
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
Next
'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
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 :)
11 Likes
86 Comments
Share
VBA, Excel, Guides
Pivot Tables, Pivot Fields, E2P
COPY & PASTE MULTIPLE EXCESETTING THE PROPER PASSWOR
Comments
Community
Recommend 7
⤤ Share
1
Login
Sort by Best
Join the discussion…
LOG IN WITH
OR SIGN UP WITH DISQUS ?
Name
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-thefundamentals examples. These are enough to get a
beginner, like me, going.
2△
▽ • Reply • Share ›
Chris
Mod
> Kiwi overseas • 3 years ago
Thank you for the feedback Kiwi!
△ ▽ • Reply • Share ›
Marcel Haller • a year ago
A realy straight forward tutorial ! Many thanks, this is very
usefull and perfectly explained !
1△
▽ • Reply • Share ›
Chris
Mod
> Marcel Haller • a year ago
Thanks for the feedback Marcel and I'm so glad to
hear this post helped you!
△ ▽ • Reply • Share ›
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.
1△
▽ • Reply • Share ›
Chris
Mod
> dan • 3 years ago
I'm currently writing an article about this. I will let you
know when it is posted.
△ ▽ • Reply • Share ›
Chris
Mod
> Chris • 3 years ago
Just posted an article on VBA and Advanced
Filters. This should help you add the
capability of filtering specific criteria.
http://bit.ly/19q2iBz
△ ▽ • Reply • Share ›
Alex > Chris • a year ago
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
If Itm1.Name = "Texto1" Then
'Nothing
ElseIf Itm1.Name = "Texto2" Then
'Nothing
...
...(Repeat code)
...
Else
Itm1.Visible = False
End If
Next
End Sub
1△
▽ • Reply • Share ›
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 • Share ›
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
△ ▽ • Reply • Share ›
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 & "!" &
see more
see more
△ ▽ • Reply • Share ›
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
see more
△ ▽ • Reply • Share ›
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
△ ▽ • Reply • Share ›
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 • Share ›
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 • Share ›
Lori > Marcel Haller • a year ago
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.
△ ▽ • Reply • Share ›
Marcel Haller > Lori • a year ago
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
ThisWorkbook.RefreshAll
End Sub
Hope this will help you,
Marcel
△ ▽ • Reply • Share ›
Marcel Haller > Lori • a year ago
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 • Share ›
Marcel Haller • a year ago
Hi Lori, I can suggest you this video, which answer your
question and is also very well explained:
▶
△ ▽ • Reply • Share ›
Lori > Marcel Haller • a year ago
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?
△ ▽ • Reply • Share ›
Lori > Lori • a year ago
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
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 • Share ›
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 • Share ›
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 • Share ›
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 • Share ›
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
△ ▽ • Reply • Share ›
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 • Share ›
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 • Share ›
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 • Share ›
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. :-)
△ ▽ • Reply • Share ›
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 • Share ›
EH Chew • 2 years ago
Cool stuff... I love it :) I found some of them very useful.
Thanks
△ ▽ • Reply • Share ›
Chris
Mod
> EH Chew • 2 years ago
Glad you found it useful!
△ ▽ • Reply • Share ›
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!
△ ▽ • Reply • Share ›
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 -
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-mmmyy")
End With
Next PT
Next WS
End Sub
△ ▽ • Reply • Share ›
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 • Share ›
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 • Share ›
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
△ ▽ • Reply • Share ›
Arputharaj Vincent • 2 years ago
Hi Chris,
Hope you are doing Good.
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 • Share ›
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 • Share ›
Chris Mod > Cynthia Roxana Panez Velazco
• 2 years ago
Yes, you will just need to use the same PivotCache
when creating your other pivot table
△ ▽ • Reply • Share ›
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 • Share ›
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...
△ ▽ • Reply • Share ›
Chris
Mod
> John W • 2 years ago
You will want to setup an ActiveX combo box and
then when someone makes a selection it would
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.
Source Exif Data:
File Type : PDF File Type Extension : pdf MIME Type : application/pdf PDF Version : 1.4 Linearized : No Page Count : 22 Creator : Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/61.0.3163.100 Safari/537.36 Producer : Skia/PDF m61 Create Date : 2017:10:04 01:05:50+00:00 Modify Date : 2017:10:04 01:05:50+00:00EXIF Metadata provided by EXIF.tools