Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Pivot Table Refresh VBA Event

Brian,

Sorting:
You can control the pivot's sorting by setting the Advanced Options in
the Field Settings for each row-, column- or page field.

AddingRows to source automatically:
Define a name for the table on which the Pivot is based, AND have that
name use a dynamic formula to include allrows/columns.
Name: dnPivotsource
RefersTo: =Offset(Sheet2!$a$1;0;0;counta($a:$a);counta($1:$1 ))

Next: backclick in the PivotWizard to change the Pivot's source
replace the range adddress with the =dnPivotSource 9use [F3] to list
available names.

Now a simple refresh is enough to update the Pivot if you've added some
rows.

cheerz!

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

"brian" > wrote:

> Hello,
>
> I have a pivot table with auto-refresh when the workbook
> is opened. If I add a new row to my source table, the
> pivot table pulls it in, but it puts it at the bottom of
> the pivot table even though I have an "order by" in my
> query.
>
> I was hoping there was an event that could be intercepted
> after the pivot table has done its refresh to run a sort
> macro I created.
>
> Thanks in advance.


Post your answer or comment

comments powered by Disqus
I am having trouble getting visual basic to update a pivot table.
(using manual refresh on the same pivot works just fine).

I have recorded a macro of the manual procedure, but it fails to work when used in a visual basic program:


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

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have tried using code presented elsewhere in these forums
eg http://www.ozgrid.com/VBA/pivot-table-refresh.htm

but the table stubbornly refuses to update.

The pivot's source data range is dynamic and works fine.

I think I'm missing something obvious or I have have a bug. Thanks for any advice.

This seems like it should be fairly easy to do but is eluding me!

I have a Pivot Table feeding a chart that needs specific formatting

However, after a Pivot Table refresh, the chart loses any custom formatting and returns to the default formatting.

(I know I can create custom user-defined charts but I don't want to do that here)

What I would like to happen is for a (chart formatting) macro to automatically run after a pivot table refesh occurs. It should only occur on the one worksheet that contains the Pivot Table and associated chart and should run the macro after a refresh REGARDLESS of whether the data within the table was updated or not.

Can anyone help me with the VBA code and which area within the VBA Project Manager the code should be placed?

Thanks in advance for any responses

~CJ

Hello,

I am trying to create a system consisting of:
A dropdown menuA pivot tableA chart
The idea is that selecting a field from a Dropdown menu, I change the fields in the pivot table (so it changes its display, let's say from having "clients" in the row labels to "locations") and then I get the data from a certain row of the pivot table to create a chart.

I know how to create pivot tables through VBA, and I know how to refresh them, but I don't know how to control the fields I wish to show in the pivot table. I am sure there might be a way.

I know that I can add fields with this instruction:

So I suppose that "Line of Business" is above "model" in the "Row Labels" box, and the same
would be with column labels.

To change the fields, I used the macro recorder and obtained this code:

 
ActiveSheet.PivotTables("PivotExpenses").PivotFields("Programs").Orientation = xlHidden
With ActiveSheet.PivotTables("PivotExpenses").PivotFields("Employee")
        .Orientation = xlRowField
        .Position = 1
End With
The problem is that I would like to know the fields I have in the pivot table in order to always keep control of what I have in each part. If this isn't possible, maybe I can just empti the rows/columns field and re-fill them with the fields I want.

I would much appreciate if someone could lend me a hand in this issue.

Hi Guys,

I've got the below code that I recorded on the macro recorder (ugh) it worked fine but now it's broken and it totally confuses me! See the bottom of the post for a link to mrexcel where there's a copy of the source data

It's working but it's giving me:Data Export Import Import/Export (blank) Grand Total Count of 00 - 30 Days 3473 3398 1 6872 Count of 31-45 Days 3473 3398 1 915 7787 Count of 46-60 Days 3473 3398 1 915 7787 Count of 61-90 Days 3473 3398 1 915 7787 Count of 91-180 Days 3473 3398 1 915 7787 Count of 180 Days and Over 3473 3398 1 6872

What I need it to do is give me the SUM of the data not the count. I also need it not to have a blank column as that's strange?! I just literally need import and export. This is what I get for using the recorder but I literally had no idea on pivot tables and VBA!

Code:pivot source data.xls


	VB:
	
 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have posted this on mr excel under: http://www.mrexcel.com/forum/showthr...27#post3001227

Please help if you can, much appreciated!

I need help. I have a workbook (WB1) with a pivot table that "Gets External Data" from another workbook (WB2) that is monitoring a piece of equipment. These were designed so WB2 is always open on a network computer collecting the data and WB1 can be opened by any other computer on the network to view and manipulate the data. When the feeder workbook (WB2) is closed, the pivot table refreshes with out any issues. When the feeder workbook (WB2) is open, the pivot will refresh, but a 3rd workbook is opened with all of the pivot background data. The problem as I said is that WB2 is always open. How do I keep this 3rd workbook from opening??

I have a very strange problem with a pivot table in Excel 2007.
The source data is in a table in another workbook, stored on a shared network drive.

We have 7 PCs in our office, and on 6 of them the pivot table refreshes fine. On the other, it always comes up with an error message that the reference is not valid, even if the source workbook is open.

This particular PC is connected by a cable, so it can't be a wireless network problem.

All the PCs have got all updates installed, and as far as I can see the Trust Centre settings are the same (the problem PC can run macros from the same folder without problems).

The really annoying thing is that the user of the problem PC is the only person who actually needs to refresh the data as part of her job.

Can anyone suggest anything?

It's possible to change by VBA pivot table fields?
For example - change month and year in pivot table by VBA.
******** ******************** ************************************************************************>Microsoft Excel - TESTKART.xls___Running: xl2002 XP : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCD1MONTH2  2YEAR2007  3    4Sum of a   5NAMETotal  6AAA1  7BBB1  8CCC1  9DDD1  Sheet3 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

I'm hoping someone can point me in the right direction to some instructions or sample code on how to build a pivot table from multiple consolidation ranges using VBA.

I'm able to build normal pivot tables with VBA, however, I've never tried doing the same with multiple consolidation ranges and I can't find any online help.

Many thanks in advance.

Hello Everyone!

I need a second set of eyes to help me with a 1004 error that occurs when I build a pivot table by VBA.

The error reads: Run time error 1004. THe Pivot Table field name is not valid. The layout of my worksheet is as follows

cell B7: rowdate, cell c7: date, d7:dialed_number, e7:description, f7:area_code,g7:calls_offered

above these headings is a merged heading extending from column b to g with the title "SBT" however in my code I have set the range not to include this merged section.

Please let me know your thoughts:

Code:
Sub MultiPivotTable()
' This procedure will create multiple Pivot Tables for the data contained within the "RAW" sheet

Dim WSD As Worksheet
Dim WSO As Worksheet
Dim WSR As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Set WSD = Worksheets("Pivotdata")
Set WSO = Worksheets("RAW")
Set WSR = Worksheets("RHP")

' Delete any prior pivot tables within the workbook

For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT

' Define the range of data to be included within the Pivot Cache
FinalRow = WSO.Cells(65536, 2).End(xlUp).Row
Set PRange = WSO.Cells(7, 2).Resize(FinalRow, 6)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
           SourceData:=PRange.Address)

' Create a canvas or blank Pivot Table based upon what is in the Pivot Cache

Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Range("B5"), _
                TableName:="PivotTable1")
                
' Set up the layout of the Pivot Table and to diable the automatic calculation while each field is added

PT.ManualUpdate = True

' Set up the Row and Column Fields

PT.AddFields RowFields:=Array("Dialed_Number", "Description", "Area_Code"), ColumnFields:="Date"

' Set up the Data area of the PivotTable

With PT.PivotFields("Calls_Offered")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 1
End With

' All settings have been made now the calculations can be made.  Then we will turn the calculation switch "off" again to make
more changes

PT.ManualUpdate = False

PT.PivotSelect "Description[All;Total]", xlDataAndLabel, True
Selection.Delete

PT.ManualUpdate = True
' Now the best thing to do is to move the data to an area where there are just values and no Pivot Tables associated with it
'
PT.TableRange2.Offset(1, 0).Copy
WSR.Range("B5").PasteSpecial xlPasteValuesAndNumberFormats
' Clear the original Pivot Table & the Pivot Cache

PT.TableRange2.Clear
Set PTCache = Nothing

End Sub


I am using the Pivot Table "Refresh" to update the table. However, the table
does not update to reflect the new source data (I paste in the new data -
each time I paste in new data there are more rows). Does the Refresh only
look at the orginal range of data ?

Is there an easy way to have the Pivot Table update as I add more source
data ?

Thank you in advance.

I have a somewhat large file that I'm trying to dynamically subtotal
with a pivot table and retreive data via vba and put it in another
workbook (2 open at once). There are two questions I have: 1) Is the
method I'm using the most efficient or is there a better way?, and 2)
If my method is okay, then what am I doing wrong?

Okay, the details... The header has depts (potentially from 1-100 in
the columns), season-type (1-50), metric type (sales, inventory,
receipts, etc) then 26 weeks going across the top. I need to
dynamically sum differing combinations of departments by season-type
for a given metric by week. (Not all possible depts or season types
will always exist.) I've created a pivot table with vba and then can
easily update the pivot table with the combinations that I want to see,
but my problem becomes when I'm trying to use the .getdata command. If
a given combination doesn't exist then run-time error 1004 pops up.
I've put in an error trap and it catches some but not all (it gets
through about 7 of them????!!!!).

'dept is in the page field of the pivot table summing depts 1-25
lkup = "7/23/2005"
ssn = 11 (a variable, but 11 here for example)
metric = "Sales" (again, another variable)
div_ssn = "'Sum of " & lkup & "' " & ssn & " " & metric
Workbooks(datawb).Activate
On Error GoTo chk_error
act_sls_data = ActiveSheet.PivotTables("data_pivot") _
.GetData(div_ssn)
chk_error:
Select Case Err.Number
Case Is = 1004
act_data = 0
End Select

ThisWorkbook.Activate
range("sales1").Cells(ssn_cntr, curr_col).Offset(0, y) =
act_sls_data

Any ideas? How about Consolidate or Group or Subtotal with this amount
of data? Needs to be fast and dynamic.

Thanks!
Pete

I have a user form set to load and show upon file open that has two fields
for user parameter entry--Customer, and Start Date. Button macro pastes this
information into cells in Sheet 1 and Sheet 2, which contain queries to an
external database. Queries are set to automatically refresh when the cells
change. Sheet 3 contains 2 pivot tables, which after the pasting from the
user form is done, are coded to refresh (then user form hides). The problem
is, the pivot tables refresh before the queries refresh, because the user has
to select the "Enable Automatic Refresh" button after the user form hides.

Can I use VB code to enable the automatic refresh when the sheet opens, and
then time the refresh of the pivot tables to follow the queries' refresh?

Hi,

I regularily build a pivot table using VBA.
I now want to group countries into regions within the pivot table, and I want to automate that using vba.
For simplicity, assume I have the countries Germany, Netherlands, Belgium, Luxembourg, Norway and Sweden; and I want to create the Groups BeNeLux and Scandinavia.
(In reality, I have 150 countries, one group of 10 countries, 2 countries I want to show individually, and the rest of the world I want to group together)

I know I could select the cells using something like cells(d2:f2).group, but that seems wrong....

I have attached a file showing how far I can get with vba and where i want to get.

I have also tried things like ".DataRange.cells(2).group by:=3" but that never led to anything....

Would be very thankful for any reply!

grouping reqs.xls

I need to group the RowField "Product Group Description" in a Pivot Table using VBA.
There may be any number of rows/descriptions in the Pivot Table. I need to group all except "Maintenance" and "Tires". I created the Pivot Table and hid Maintenance and Tires temporarily so I could group the rest of the lines as Repairs. Then I will show Tires and Maintenance again at the end. I cannot figure out how to do the grouping.

Sub MaintenancePart2()
    Dim WSD As Worksheet
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PRange As Range
    Dim FinalRow As Long
    
    Set WSD = Worksheets("Sheet1")
    Dim WSR As Worksheet
        
    ' Define input area and set up a Pivot Cache
    FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
    FinalCol = WSD.Cells(1, Application.Columns.Count). _
        End(xlToLeft).Column
    Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
        xlDatabase, SourceData:=PRange.Address)
    
    ' Create the Pivot Table from the Pivot Cache
    Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
        Cells(2, FinalCol + 2), TableName:="PivotTable1")
    
    ' Turn off updating while building the table
    PT.ManualUpdate = False
    
    ' Set up the row fields
    PT.AddFields RowFields:="Product Group Description", ColumnFields:="Data", PageFields:="FM
Client #"
    
    ' Set up the data fields
    With PT.PivotFields("Extended Price")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 1
        .NumberFormat = "$#,##0"
        .Name = "Total Spend"
    End With

    ' Ensure that we get zeros instead of blanks in the data area
    PT.NullString = "0"
End Sub

I attached sample data.

Hi Experts,

I am creating a pivot table using vba.

Here i want to present the pivot like this.

Data
ACTUAL NAME DATE STAGE SUB PROCESS "Sum of Pages
Produced" "Sum of
HOURS" "Sum of
MINUTES"
aa 4/6/2010 Proof IHP 3 16 0 10
(blank) 106 1 135
Revises4 IHP 1 2 0 6

But i am getting it like this

ACTUAL NAME USER DATE STAGE SUB PROCESS Data Total
AFatimanapoleon PG0610 4/6/2010 Proof IHP 3 Sum of Pages Produced 16
Sum of HOURS 0
Sum of MINUTES 10

ie I want to present the sum of values in column label field instead of row label field

Hi,

I am trying to create a pivot tables using VBA with different number of rows
of source data. How can I supply the number of rows as a parameter to the
following method:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="???"

It seems it doesn't like me to supply a string variable to the SourceData,
for example, SourceData:= strRange

Any other method to get around? Thanks.

Is there a way to create/automate a Pivot table using VBA. I am trying to sum/display all sales data for all employees by month. The range of the data sheet will have to depend on whatever is in the data sheet since it will constantly be updated. So there could be 10 lines of 100 lines so the pivot table would need to account for all that.

Thanks

Hi,

I have pretty stanard timestamped data in one sheet and I'd like to create a Pivot table from it in another sheet.

I'd hoped that VBA would just record the steps I was taking in Excel, but this doesn't seem to happen.

Could someone take me through creating a Pivot table using VBA?

I have created a pivot table using VBA and am now trying to make it show duplicate rows for me. THe pivot table shows jobs. If there are two jobs with the same title, i still want both jobs to show b/c they are unique to me. How can i get this to work in my pivot table???

Hello,

I am looking for some help to create a Pivot table with VBA. Basically I have a set of Forecasts to download out of a database and it always comes in the same format, however, the information, months, etc. change. I need a pivot table generated that can tell how many rows and columns are there plus adapt to the different headers....any sugestions?? I really want this pivot table to be generated when I run my formating macro.

Thanks,
Matt

I have a somewhat large file that I'm trying to dynamically subtotal
with a pivot table and retreive data via vba and put it in another
workbook (2 open at once). There are two questions I have: 1) Is the
method I'm using the most efficient or is there a better way?, and 2)
If my method is okay, then what am I doing wrong?

Okay, the details... The header has depts (potentially from 1-100 in
the columns), season-type (1-50), metric type (sales, inventory,
receipts, etc) then 26 weeks going across the top. I need to
dynamically sum differing combinations of departments by season-type
for a given metric by week. (Not all possible depts or season types
will always exist.) I've created a pivot table with vba and then can
easily update the pivot table with the combinations that I want to see,
but my problem becomes when I'm trying to use the .getdata command. If
a given combination doesn't exist then run-time error 1004 pops up.
I've put in an error trap and it catches some but not all (it gets
through about 7 of them????!!!!).

'dept is in the page field of the pivot table summing depts 1-25
lkup = "7/23/2005"
ssn = 11 (a variable, but 11 here for example)
metric = "Sales" (again, another variable)
div_ssn = "'Sum of " & lkup & "' " & ssn & " " & metric
Workbooks(datawb).Activate
On Error GoTo chk_error
act_sls_data = ActiveSheet.PivotTables("data_pivot") _
.GetData(div_ssn)
chk_error:
Select Case Err.Number
Case Is = 1004
act_data = 0
End Select

ThisWorkbook.Activate
range("sales1").Cells(ssn_cntr, curr_col).Offset(0, y) =
act_sls_data

Any ideas? How about Consolidate or Group or Subtotal with this amount
of data? Needs to be fast and dynamic.

Thanks!
Pete

Hi everyone. I am trying to create a pivot table using VBA code. I am reciveing an error, in the code though, but atually cannot pinpoint the problem. Any help would be greatly appreciated.

Sub
Automated_Pivot_Generation_Borrowings_Porrtfolio()

Const sFileInp1 As String = "105.xlsm"
Const sFileInp2 As String = "Borrowing_Portfolio_Bonds.xlsm"
Dim sPath1      As String
Dim sPath2      As String
Dim wb1         As Workbook
Dim wb2         As Workbook

sPath1 = fPath & fDate & "_157105_Reports"
sPath2 = fPath & fDate & "_157157_ReportsIBRD_Disclosure"

Set wb1 = Workbooks.Open(sPath1 & sFileInp1)
Set wb2 = Workbooks.Open(sPath2 & sFileInp2)
 
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "31-mar-2010105!R1C1:R11500C48", Version:=xlPivotTableVersion12). _
        CreatePivotTable TableDestination:=sPath2 & sFileInp2 & "Bonds", TableName:="Borrowing
Portfolio (Desk, Local,BK2BK,AMCCLT) - Bonds only" _
        , DefaultVersion:=xlPivotTableVersion12
                
With ActiveSheet
    .PivotTables("Borrowing Portfolio (Desk, Local,BK2BK,AMCCLT) - Bonds only").PivotFields ( _
        "Count of MTM_USD_11/31/2009")
        .Caption = "Sum of MTM_USD_11/31/2009"
        .Function = xlSum
    .PivotTables("Borrowing Portfolio (Desk, Local,BK2BK,AMCCLT) - Bonds only").PivotFields ("Desk "
& Chr(10) & "ID")
        .PivotItems("CLIENT").Visible = False
        .PivotItems("EQTY").Visible = False
        .PivotItems("IDA").Visible = False
        .PivotItems("IDACLT").Visible = False
        .PivotItems("IFFM").Visible = False
        .PivotItems("LOAN").Visible = False
        .PivotItems("MLOAN").Visible = False
        .PivotItems("XPOOL").Visible = False
        .PivotItems("(blank)").Visible = False
        .EnableMultiplePageItems = True
    .PivotTables("Borrowing Portfolio (Desk, Local,BK2BK,AMCCLT) - Bonds only").PivotFields
("Asset_Liability")
        .Orientation = xlColumnField
        .Position = 2
        .PivotItems("Asset").Visible = False
    .PivotTables("Borrowing Portfolio (Desk, Local,BK2BK,AMCCLT) - Bonds only").PivotFields
("Bond_Swap")
        .Orientation = xlColumnField
        .Position = 2
        .PivotItems("CSWAP").Visible = False
        .PivotItems("ISWAP").Visible = False
        .PivotItems("FALSE").Visible = False
    .PivotTables("Borrowing Portfolio (Desk, Local,BK2BK,AMCCLT) - Bonds only").PivotFields
("CM_FAS157_Levels")
        .PivotItems("3").Visible = False
        .PivotItems("N/A").Visible = False
        .PivotItems("#N/A").Visible = False
End With
       
End Sub


Hello,

I have a pivot table that is based on a SQL Server connection. The pivot table queries a database view and groups the data and sums up a few columns.

I'm facing a problem where if data changes in the underlying database, the change is not reflected in the pivot table. I want the pivot table to view the data in the underlying view; so rows deleted from the view dissappear from the pivot table, and rows added appear.

I know people have faced this problem before and some posted macros to make the pivot table refresh. I tried some of these solutions and they didn't work for me.

If someone reading this post has faced this problem and solved it, could you please tell me how you solved it and may be also post your working macro code?

Thank you.

I am trying to run a MS Query query in Excel and refreshing a pivot table
afterwards that feeds off the query using VBA. The query dumps the data into
a separate data sheet in Excel. The pivot table reads the Excel sheet (and
is not linked to MS Query directly).

Problem is that the query takes a while to run. The VBA code doesn't wait
for the query to finish before it continues to the next line and refreshes
the pivot tables. The result is that the pivot tables re-refresh but not
based on the latest data.

What I need is code that halts the execution of the VBA until all queries
have run (ie the Query application has closed). Are there other solutions
out there?

Code so far is below:-

Dim wsSheet As Worksheet
Dim qt As QueryTable
Dim pvt As PivotTable

'To update all query extracts in Workbook
For Each wsSheet In Worksheets
For Each qt In wsSheet.QueryTables
qt.Refresh
Next
Next wsSheet

'To update all pivot tables across all worksheets
For Each wsSheet In Worksheets
For Each pvt in wsSheet
pvt.Refresh
Next pvt
Next wsSheet

End Sub


No luck finding an answer? You could always try Google.