How to use Pivot Tables – Excel’s most powerful feature and also least known

175 Responses to “How to use Pivot Tables – Excel’s most powerful feature and also least known”

1. sam says:

   April 27, 2011 at 3:26 am

   Moment you use tables you are restricting it to 2007/2010
   Instead create a 2d dynamic name
   Data
   ='1'!$A$1:INDEX('1'!$1:$65536,COUNTA('1'!$A:$A),COUNTA('1'!$1:$1)) and use it in the Pivot Range

   Reply
2. Oscar says:

   April 27, 2011 at 8:24 am

   Great comment, thanks!

   Reply
3. davidlim says:

   June 23, 2011 at 2:11 am

   hi oscar,

   thanks for this cool tip!

   btw, if i have another Pivottable (using different pivotcache), can i add an additional line?

   e.g. PivotTable2 is using diffent pivotcache than PivotTable1. If you're wondering, i use different pivotcache due to date grouping (PivotTable1 = daily, PivotTable2 = 7-days grouped)

   Private Sub Worksheet_Change(ByVal Target As Range)
   Worksheets("Pivot table").PivotTables("PivotTable1").PivotCache.Refresh
   Worksheets("Pivot table").PivotTables("PivotTable2").PivotCache.Refresh
   End Sub

   thanks!

   Reply
4. Oscar says:

   June 23, 2011 at 7:23 am

   davidlim,

   Yes, that works. Make sure you have the correct sheet and pivot table names.

   Thanks for sharing!

   Reply
   • Thushara says:

     May 19, 2015 at 10:10 am

     HI Im confused on how we do this in excel 2013

5. davidlim says:

   June 24, 2011 at 11:45 pm

   hi oscar,

   i noticed there is a problem on pivottable with grouped dates.

   my Data sheet is an Excel 2010 Table. Data is daily stats, therefore i add new daily data below the Table.

   whenever new data is appended to the Table (new daily data), the PivotTable will automatically "ungrouped" (grouped Dates --> single Dates).

   I had to manually regroup the dates again.

   have you encountered this?

   Reply
6. Oscar says:

   June 27, 2011 at 11:41 am

   davidlim,

   It seems that the pivot table preserves formatting if you copy and paste data to your table or dynamic range. When you move a cell range to the last row of a table or dynamic range, pivot table formatting is lost.

   Try copy / paste next time you append new data to your table.

   Reply
7. davidlim says:

   June 27, 2011 at 11:58 pm

   hi oscar,

   thanks for the response.

   fyi, i performed copy-n-paste "value only" when i append the data.

   also, i've tried another macro: refresh pivottable when pivottable sheet is selected. <-- this doesnt have any issue :)

   anyways, this alternate macro suits me as well.

   very much appreciate your help!

   Reply
8. Nick says:

   July 11, 2011 at 3:21 pm

   Hi,
   When I try this with Excel for Mac 2011, the sheet appears to get stuck in a loop where it's recalculating the sheet over and over again. The only way to get out is to force quit Excel.

   Ideas?

   Reply
9. Sean says:

   September 30, 2011 at 2:26 pm

   Oscar,

   That seems to work a treat. because I have 3 pivot tables on individual worksheets that are not the same worksheet as the pivot data, I have placed the code on each of the pivot table sheets. I'm assuming this is correct, as it didn't work on the other pivot table sheets where I hadn't included the code.

   Any chance to could add comments to the code, so that I can work out what the subroutine is doing ?

   The help is appreciated and hopefully it will help others as well.

   Kind Regards
   Sean

   Reply
10. Oscar says:

    October 11, 2011 at 9:19 pm

    Sean,

    Thanks!

    I have added comments to the code.

    Reply
11. Oscar says:

    October 12, 2011 at 1:00 pm

    This code works for all pivotTables in a workbook.

    Copy/paste the code below to "ThisWorkbook" code module in project explorer (Alt+F11)

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    
    Dim ptc As PivotTable, Value As Variant, Rng As Range
    
    For Each ptc In Worksheets(Sh.Name).PivotTables
        If Not Intersect(Target, Range(ptc.TableRange1.Address)) Is Nothing Then
            If Left(Target.Value, 7) = "https://" Then
                ActiveWorkbook.FollowHyperlink Address:=Target.Value, NewWindow:=True
            End If
        End If
    Next ptc
    
    End Sub
    

    Get the Excel *.xlsm file
    Hyperlinks-in-pivot-tables-in-a-workbook.xlsm

    Reply
    • Brian says:

      April 5, 2013 at 3:54 pm

      The follow code from above works well except that the first time I press with left mouse button on a hyperlink, I get a Run Time Error 13. When I reset and press with left mouse button again, it's fine. What could be the cause of this. Code is below:

      Sub Worksheet_SelectionChange(ByVal Target As Range)
      Dim ptc As PivotTable
      'Find every pivotTable in ActiveSheet
      For Each ptc In ActiveSheet.PivotTables
      'Check if selected cell is a pivot table cell
      If Not Intersect(Target, Range(ptc.TableRange1.Address)) Is Nothing Then
      'Check if the cell value begins wih https://
      If Left(Target.Value, 7) = "https://" Then
      'Follow hyperlink using cell value as hyperlink address
      ActiveWorkbook.FollowHyperlink Address:=Target.Value, NewWindow:=True
      End If
      End If
      Next ptc
      End Sub

    • Oscar says:

      April 8, 2013 at 8:26 am

      Brian,

      Run Time Error 13 - Type mismatch

      Which line is the problem?

    • Christian says:

      April 26, 2013 at 3:23 pm

      Hey Brian,

      I get the same Error 13, when I select a range of cells.
      Following Line is (according to Excel) the problem:
      If Left(Target.Value, 7) = "https://" Then

      Best,
      Christian

    • Christian says:

      April 26, 2013 at 3:28 pm

      Sorry, Oscar of course :)

    • Oscar says:

      April 29, 2013 at 9:48 am

      Christian,

      You are right. If you select more than one cell you get an error.

      Try this:

      
      Sub Worksheet_SelectionChange(ByVal Target As Range)
      Dim ptc As PivotTable, Value As Variant, Rng As Range
      If Target.Cells.Count = 1 Then
          For Each ptc In ActiveSheet.PivotTables
              If Not Intersect(Target, Range(ptc.TableRange1.Address)) Is Nothing Then
                  If Left(Target.Value, 7) = "https://" Then
                      ActiveWorkbook.FollowHyperlink Address:=Target.Value, NewWindow:=True
                  End If
              End If
          Next ptc
      End If
      
      End Sub
      
      
      

      Thanks for commenting!

    • Nicky PIke says:

      June 12, 2014 at 9:14 pm

      Oscar,

      The original code works well for me, however, I also get the mismatch error in selecting multiple cells. I tried the new code that you posted to fix this error, but when using it, the follow hyperlink no longer works. Any ideas?

12. Tim says:

    October 13, 2011 at 8:43 pm

    Hi Oscar,

    I was able to get this to work on Excel 2011 (Mac) but when I open up the same file in Excel 2010 (PC) the macros don't run. I've enabled Macros and set the security settings to run all Macros.

    Essentially what I have is multiple pivot tables across 6 worksheets all using the Data worksheet as the data source.

    I tried to update the Macro to use the code you mention in the post "https://www.get-digital-help.com/2011/06/13/auto-refresh-a-pivot-table-in-excel/". Using this the macros runs but I get a run-time error '1004': Application-defined or object defined error.

    Any thoughts on what I can do in Excel 2010 to get my macro to run?

    Thanks - Tim

    Reply
13. deepak ji paulson says:

    October 26, 2011 at 2:18 pm

    The Attachement has no Excel files :(

    Code is not working with xl 2007

    Reply
14. Oscar says:

    October 26, 2011 at 3:14 pm

    deepak ji paulson,

    I opened both files and they work here. I am using excel 2007.

    Reply
15. RE says:

    December 16, 2011 at 10:20 pm

    I'm not sure that I'm in the right location for this, and I'm working handicapped with v2003 and I don;t know vba.

    I have an xml file thats exported from a third party tool. I import that into excel and add a column to the far left. In this column I do a match fucntion. This function matches specific data in one column of the xml data to "sheet2" which containes a list of codes that I care about. Then I create a pivot table that uses the match (1-30), ingnores blanks and NAs to filter my pivot. What gets displayed is all the information I care about. Works great. Problem is I'm doing this on a weekly basis and realize there must be a better way to link to the xml, still make my match to my codes and create a piviot table.

    Question is how to acheive this. No luck treating the xml as a database. Excel is not seeing it.
    Please advise and thanks for the expertise.

    Reply
16. Oscar says:

    December 20, 2011 at 10:41 pm

    RE,

    Is it possible in excel 2003 to import xml using Get external data, from other sources?

    Import multiple XML data files as external data

    If it is then you should be able to refresh external data source if the xml file name is the same.

    Reply
17. pofflk says:

    January 10, 2012 at 10:26 pm

    This is a simple and beautiful solution! Thanks!

    Reply
18. Jeff says:

    May 8, 2012 at 4:02 pm

    My pivot table is called "PivotTable"

    I created a macro, put the code in:Sub ChangeDataSource()
    With ActiveSheet.Shapes(Application.Caller).ControlFormat
    ActiveSheet.PivotTables("PivotTable").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
    .List(.Value)
    End With
    End Sub

    Now when I try to change the data source via drop-down i receive this error: Run-time error '1004' Reference is not valid.

    Reply
19. Andrius says:

    May 9, 2012 at 11:27 am

    ...all ok with exampke untill the moment of questions: if for e.g. i have two uniques values with the same amount of records.. then dividing will be nothing worse....

    Reply
20. Andrius says:

    May 9, 2012 at 11:27 am

    ..."nothing worth" i have meant (sorry :) )

    Reply
21. Oscar says:

    May 14, 2012 at 12:43 pm

    Jeff,

    I am guessing, the names in the combo box don´t match the table names in your workbook?

    How to find the table names
    1. Select a cell in a table
    2. Go to tab "Design" on the ribbon

    Reply
22. Oscar says:

    May 14, 2012 at 1:17 pm

    Andrius,

    Yes..

    I am not sure I understand.

    Reply
23. Charts, Dashboards and More – Best of Excel on the Web (May 2012) | Launch Excel says:

    May 15, 2012 at 4:57 pm

    [...] Disable autofit column widths for all Pivot Tables in a sheet (VBA) [...]

    Reply
    • Bob says:

      November 30, 2012 at 7:29 pm

      The code above did not work for me. I pasted into the suggested location and the pivot tables are still autofitting on update.

      Is there something I am missing?

      Thanks!

    • Oscar says:

      December 4, 2012 at 1:38 pm

      Bob,

      I tried the code and it works in in excel 2007 and 2010.

      Did you double press with left mouse button on Thisworkbook in the project explorer window?

      

24. palie says:

    June 19, 2012 at 9:48 am

    Oscar, you are awesome! millions of thanks

    Reply
    • Harshitha says:

      October 30, 2012 at 9:17 am

      HI,

      THe link is working fine but if i need to press with left mouse button on the Text or value column and if the hyperlink has to be opened what could be the solution for that?.

    • Oscar says:

      October 31, 2012 at 2:47 pm

      Harshitha,

      Sub Worksheet_SelectionChange(ByVal Target As Range)
      Dim ptc As PivotTable, Value As Variant, Rng As Range
      
      For Each ptc In ActiveSheet.PivotTables
          If Not Intersect(Target, Range(ptc.TableRange1.Address)) Is Nothing Then
              If Left(ptc.TableRange1.Cells(Target.Row - ptc.TableRange1.Row + 1, 1).Value, 7) = "https://" Then
                  ActiveWorkbook.FollowHyperlink Address:=ptc.TableRange1.Cells(Target.Row - ptc.TableRange1.Row + 1, 1).Value, NewWindow:=True
              End If
          End If
      Next ptc
      
      End Sub
      

      You probably need to change this line:

      If Left(ptc.TableRange1.Cells(Target.Row - ptc.TableRange1.Row + 1, 1).Value, 7) = "https://" Then

      Bolded value is the column number for the links in the Pivot table range.

25. JamesO says:

    June 24, 2012 at 6:15 pm

    There is a much much easier way to keep the pivot table dynamic guys!!

    Simply reference only the top row of your data. Leave out the column references.

    Example- Before: $A$1:$Z$5000

    Example- After: $A:$Z

    Notice we are completeley leaving the numbers out...works like magic!

    Reply
26. Oscar says:

    June 25, 2012 at 8:42 am

    JamesO,

    Yes, it works but only when the data begins on row 1.

    I have not tried this in excel 2003 but if it works you can skip the "named range" part.

    You still have to press with right mouse button on your pivot table and select "refresh" to refresh pivot table.

    Thank you for commenting!

    Reply
27. Jennifer Long says:

    July 6, 2012 at 11:02 pm

    I am also getting a run-time rror'1004: Application-defined or object-defined error on:

    ActiveSheet.PivotTables("PivotTable2").PivotTableWizard SourceType:=x1Database, SourceData:= _
    .List(.Value)

    I changed the pivot table name as instructed above, but that has not corrected the error. Any suggestions? Thanks.

    Reply
28. luluk tri says:

    July 13, 2012 at 3:54 am

    Hi oscar,

    Thank you for very helpfull function. Please share how to save. Because, After create this , save and close this excel, and I open this excel file, but this VB didn't exist.

    Please inform me how to save.

    Thanks a lot

    Reply
    • Oscar says:

      July 13, 2012 at 4:33 pm

      luluk tri,

      Save as an *.xlsm file

      

    • luluk tri says:

      July 20, 2012 at 9:28 am

      Hi Oscar,

      Thank you for your advice. Once more, please assist me.
      All of macro was run smoothly. But, I found that "UNDO" was deactivated by system. How to activated "UNDO".

      Thanks.

      luluk_tri

    • Oscar says:

      July 23, 2012 at 2:49 pm

      luluk_tri,

      Yes, the undo stack is lost.

      Undoing A VBA Subroutine

29. Oscar says:

    July 14, 2012 at 7:13 pm

    Jennifer Long,

    Is the attached file working?
    Did you spell the table names correctly? (Read my answer to Jeff)

    I get the same error if I use a table name that does not exist.

    Reply
30. Ernie says:

    July 19, 2012 at 10:34 pm

    Hi,

    I was wondering if there is a way to refresh when the data that is being changed is in a controlbox. I'm using active x controlboxes to select from a list which in turn will adjust prices and so forth. If I change a selection in the controlbox the pivot table is not updated. The script is working if I change a data entry manually and hit enter to tab to move to a new cell.

    Thanks,

    Ernie

    Reply
    • Oscar says:

      July 23, 2012 at 2:55 pm

      Ernie,

      Press with right mouse button on sheet name. Press with left mouse button on "View code". Change combobox name. Copy/Paste code.

      Private Sub ComboBox1_Change()
          Worksheets("Pivot table").PivotTables("PivotTable1").PivotCache.Refresh
      End Sub
      

31. David Rogers says:

    August 7, 2012 at 11:05 am

    Simple, straightforward information. Thanks very much.

    Reply
32. G says:

    August 16, 2012 at 9:13 pm

    I am getting run-time error '1004'
    whenever i update the Data sheet.

    Reply
    • Oscar says:

      August 17, 2012 at 11:41 am

      G,

      Did you change the names (bolded)?

      Worksheets("Pivot table").PivotTables("PivotTable1").PivotCache.Refresh

      Pivot table <-- Sheet name PivotTable1 <-- Pivot table name

33. lazyvba says:

    August 20, 2012 at 8:09 pm

    Hello,

    I did automate the process in a vba macro that add a unique count datafield in the pivot, automatically updated when you change the configuration of the pivot.

    Let me know your thoughts.

    https://lazyvba.blogspot.com/2010/11/improve-your-pivot-table-to-count.html

    Reply
    • Oscar says:

      August 22, 2012 at 2:26 pm

      lazyvba,

      I did try your code and it seems to work with my example pivot table!

      It converted my data to a table and added new columns, maybe you mentioned that on your website?

34. Joe says:

    August 22, 2012 at 7:11 pm

    Great tip. However taking it a step further, can the pivot table only update when 1 cell is changed, and not any cell on the tab.

    Example say I only want the table to refresh when cell AB4 is changed.

    How would you do that?

    Reply
    • Oscar says:

      August 22, 2012 at 8:23 pm

      Joe,

      Private Sub Worksheet_Change(ByVal Target As Range)
      If target.address = "$AB$4" then  
         Worksheets("Pivot table").PivotTables("PivotTable1").PivotCache.Refresh
      End If
      End Sub
      

    • Janu says:

      August 26, 2014 at 5:41 am

      can you please explain the function of cache here

35. Ben says:

    August 29, 2012 at 9:16 am

    Thanks a lot! This solution saved me some hours.

    Reply
36. Oscar says:

    August 29, 2012 at 9:22 am

    Ben,

    Thanks for commenting!

    Reply
37. Sachin says:

    September 7, 2012 at 10:59 am

    Macro works but then it is preventing undo..any workaround for that?

    Reply
38. Oscar says:

    September 10, 2012 at 2:13 pm

    Sachin,

    it is possible to undo a macro but I am not sure about the undo stack:
    https://www.j-walk.com/ss/excel/tips/tip23.htm
    https://www.jkp-ads.com/Articles/UndoWithVBA00.asp

    Reply
39. William J. Ryan says:

    September 18, 2012 at 9:41 pm

    I may be complicating so if so I apologize up front! I want to autopopulate tab 2 with select information from tab 1 if "Yes" is inut on tab 2, column A.

    I have a lot of data in tab 1 but the actionable items are in tab 2 only if "Yes". I may have 50 rows in tab 1 but only need 3 rows in tab 2 and I don't need all columns in tab 1, only a few.

    What is the best way to automate this process? Thank you!

    Reply
    • Oscar says:

      September 19, 2012 at 3:42 pm

      William J. Ryan

      Can you provide some example data from both sheets?

40. Talib says:

    September 25, 2012 at 6:12 am

    after applying the method the "undo" command doesn't work..any solution??

    Reply
41. Jim says:

    October 10, 2012 at 10:09 pm

    I can't seem to ge tthis to work when the data and pivot table are on the same sheet. Is tha possible

    Reply
42. Anselmus Basunanda says:

    October 11, 2012 at 8:03 am

    Dear Oscar,
    Is it possible to make change not pivot table, but pivot chart using drop down list of several pivot table ?

    Reply
43. Filipe says:

    October 26, 2012 at 3:06 pm

    Does not seems to work with pivot table in the same sheet here either... any suggestions? :)

    Reply
44. Bill says:

    October 29, 2012 at 1:29 pm

    Hi Oscar,
    I found your code very helpful and easy to apply despite that I am a VBA bigginer.
    In my case the pivot table is in a different worksheet named "data2" , from the Combo box which is in "Data1".
    Is there any way to modify you code so I can still update the pivot table?

    Many Thanks.

    Reply
45. PMS says:

    October 31, 2012 at 7:30 am

    Hi,
    I want to auto update the pivot table.The issue is I'm not running vb in excel .I have update the pivot table with my third party software vb tool.I have to define my pivot table folder path and also i need to refresh the pivot table

    Reply
46. Oscar says:

    October 31, 2012 at 10:30 am

    Bill,

    Yes there is!

    Sub ChangeDataSource()
    
    With ActiveSheet.Shapes(Application.Caller).ControlFormat
        Worksheets("data2").PivotTables("PivotTable1").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
        .List(.Value)
    End With
    
    End Sub
    

    Reply
    • Bill says:

      November 1, 2012 at 5:22 pm

      Many Thanks Oscar,
      Works fine.

47. Oscar says:

    November 1, 2012 at 9:54 am

    Filipe,

    Make sure you have changed the sheet name and the pivot name (bolded):

    Worksheets("Pivot table").PivotTables("PivotTable1").PivotCache.Refresh

    PMS,

    I have no clue.

    Reply
48. Filipe says:

    November 2, 2012 at 10:30 am

    Oscar,

    Thank you so much for your help.

    I have specified the correct workbook and piveot table, and, for a short time, I can see the values to change... The problem is that this seems to make excel to run an infinite refreshing loop which causes excel to crash.

    Any sugestions?

    Have a great day,

    Reply
    • Oscar says:

      November 6, 2012 at 8:53 am

      Filipe,

      If pivot table and source data are located on the same sheet excel goes into an infinite loop.

      This code takes care of that problem:

      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim pt As PivotTable
          Set pt = Worksheets("Pivot table").PivotTables("PivotTable1")
          If Intersect(Target, pt.TableRange1) Is Nothing Then
              Worksheets("Pivot table").PivotTables("PivotTable1").PivotCache.Refresh
          End If
      End Sub
      

      Get the Excel *.xls file
      auto-refresh-pivot-table1.xls

    • David says:

      October 3, 2013 at 7:29 pm

      Oscar

      I have an (Excel 2010) expense Claim report design which has the Expense Data (Table2) on the same sheet (Expense Claim) as a pivot table (pivottable1)which summarises the claims by expense type. Your suggested macro above works nicely to auto refresh the pivot anytime I add in an extra expense claim item, but to allow others in the company to use it I need to protect areas of the claim form - otherwise they mess up. The Auto Refresh macro doesnt work when I protect the sheet. Is here a few lines I can insert into the Macro to fix this?

49. Joao says:

    November 5, 2012 at 10:47 pm

    I'm having the same problem. after update 1 cell the pivot table refreshes but i get the message: Run-Time Error -2147417848 (80010108) Method 'Refresh' of object PivotCache Failed.

    Reply
50. Oscar says:

    November 6, 2012 at 9:59 am

    Joao, read my answer to Filipe.

    Reply
51. Filipe says:

    November 6, 2012 at 5:25 pm

    Hi Oscar,

    That is brilliant, thank you so much.

    The specific workbook I have happens to have two pivot tables populated from the same table source, and in that case I could not tweak your code to make this possible.. it keeps refreshing for long time.. With only one pivot table works really great.

    Suggestions are very welcome,

    Reply
    • Oscar says:

      November 7, 2012 at 3:08 pm

      Filipe,

      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim pt1 As PivotTable, pt2 As PivotTable
          Set pt1 = Worksheets("Pivot table").PivotTables("PivotTable1")
          Set pt2 = Worksheets("Pivot table").PivotTables("PivotTable2")
              If Intersect(Target, pt1.TableRange1) Is Nothing And Intersect(Target, pt2.TableRange1) Is Nothing Then
                  Worksheets("Pivot table").PivotTables("PivotTable1").PivotCache.Refresh
              End If
      End Sub
      

    • Sam says:

      November 7, 2012 at 3:31 pm

      Thanks Oscar for the solution,
      would you mind explaining why there will be an infinite loop when the pivot tables and source are located in the same worksheet?

    • Sam says:

      November 7, 2012 at 4:58 pm

      Oh, I seem to get it.
      Is that because after refreshing, another change event is triggered?

    • Oscar says:

      November 8, 2012 at 10:26 am

      Sam,

      Yes, when the pivot table refreshes the change event is launched again.

    • Filipe says:

      November 7, 2012 at 5:50 pm

      Great, great, great, thank you so much, this is great!

52. hosny says:

    November 13, 2012 at 11:26 am

    Hi Oscar,

    Thank you for the post. This is great!
    I come an issue when using this though. I have a workbook with 2 sheets and derive 5 pivot tables from each sheet (10 pivot tables). I tried the code with 5 lines (1 line for each pivot table) in each sheet initially, then changed it to a single line for each sheet. But still excel continuously executes the query and doesn't stop. I need to press 'Esc' and 'Debug' in order to stop it.

    Any suggestions?

    Reply
    • hosny says:

      November 13, 2012 at 1:20 pm

      Looks like Excel has a single Pivot cache for all 5 charts from a single sheet. Thus I used a single line of code to refresh the pivot cache. Can you please confirm if this is correct?

      I think the continuous execution of the query has stopped for some reason and it works fine now. Thank you very much.

53. tania says:

    November 19, 2012 at 3:45 pm

    great idea! thank you

    Reply
54. Guru says:

    November 20, 2012 at 8:36 am

    Hi Oscar,
    Thanks for your postings, I hv used this short sub-routine and it works.

    My problem starts only when i applied a sort order (in Descending Order) on the first column of my pivot table that contains a date. Everytime that a new data is added on the source sheet, my sort order in the pivot table scrambled and out of order.

    My question to you now is - How to keep the sort order intact when there is/are data change? Can you add on above sub routine code something that when data refreshes, the sort order refreshes also to DESC order?

    Reply
    • Oscar says:

      November 28, 2012 at 9:29 am

      Guru,

      My problem starts only when i applied a sort order (in Descending Order) on the first column of my pivot table that contains a date. Everytime that a new data is added on the source sheet, my sort order in the pivot table scrambled and out of order.

      I tried adding dates in excel 2010 the dates are not out of order. But this macro should do it:

      Private Sub Worksheet_Change(ByVal Target As Range)
          With Worksheets("Pivot table").PivotTables("PivotTable1")
              .PivotCache.Refresh
              .PivotFields("Date").AutoSort _
                    xlDescending, "Date"
          End With
      End Sub
      
      

55. Hyperlinks in Excel Pivots? says:

    January 6, 2013 at 3:55 pm

    [...] Use a VBA solution, as described here: Follow hyperlinks in a pivot table | Get Digital Help - Microsoft Excel resource [...]

    Reply
56. Sanjoy says:

    January 18, 2013 at 5:56 am

    Boss you are genius. Hv been searching this for quite some time.

    Reply
57. Sven says:

    January 25, 2013 at 4:42 pm

    Oscar,

    Thanks a lot for all the useful information you provide here. I opened your example file and it worked, although it says on the top it runs in [Compatibility Mode].
    Now I don't manage to get the code executed in my orginal file. I even copied your worksheets into my Excel file, and then the automatic update stops to work again. Am I right assuming that nothing with the code is wrong, but some settings in my Excel prevent it from running the code?

    Any help where I could look is highly appreciated!

    Reply
    • Oscar says:

      January 29, 2013 at 12:05 pm

      Sven,

      did you check the sheet and pivot name? I assume you have other names?

      Worksheets("Pivot table").PivotTables("PivotTable1").PivotCache.Refresh

    • Sven says:

      January 29, 2013 at 12:47 pm

      Hi Oscar, yes I did. I also copied the tabs from your example workbook into my file, they stopped to work too. So must be a problem with my workbook settings? But it is a macro enabled xlsm-file...? I am obviously clueless! :)

58. bert says:

    February 21, 2013 at 3:55 am

    Hi Oscar!

    This is really helpful. I have one question though. How do you tweak the codes when the source data is stored in a separate workbook?

    Thanks!

    Reply
    • Oscar says:

      February 21, 2013 at 6:49 pm

      bert,

      Workbooks("Book1.xlsx").Worksheets("Pivot table").PivotTables("PivotTable1").PivotCache.Refresh
      

    • Bert says:

      March 17, 2013 at 4:33 pm

      Hi Oscar!

      Thanks a lot for your help! I already got this to work in some of my routine jobs! I'm searching your site for other things that can help me more. Great job for you!

    • Janu says:

      August 26, 2014 at 5:40 am

      Hi Oscar,
      Good day

59. Richard Harris says:

    February 26, 2013 at 3:31 am

    Hi Oscar

    You little genius you. Thank you so much, exactly what I wanted (Mac 11 User)

    Reply
60. Green says:

    March 4, 2013 at 6:20 pm

    Hello,

    Have a pvot table for monthly reporting of #sales made by each sales paerson. Data is generated from a SQL quarry, capture the data and recreat the the table of report by pasting is to Excel. This done every month, I wonder if this table can be made dinamic so that it can pull the data each month from the quarry?

    Regards,
    G

    Reply
61. Najeeb says:

    March 15, 2013 at 12:12 pm

    Hi dear,

    How to Number form Sentencce which in celll

    Example:- In cell a sentacne

    Problem Summary(Cell 1)

    spk to mr r k chabra call 4 want to shift the conection in same room alt no is 91-9818786350 plz chk dn do need full asap.............

    REQUEST RELATEDTariff MigrationContact Person:tarun Contact Number:9871431939Authentication Done:YesCheck OG Status - activeSegment Check:YesCurrent Plan :Airtel - UL - Smart One Combo 8GB 799(2Mbps/256Kbps) DSL Plan (Subscription

    Disturbance-All Times (Cell 5)
    Ateeq
    91-9891608537

    Reply
62. j moore says:

    May 1, 2013 at 3:01 am

    I need a little help on how to automate grouping (categories are: 1-30, 31-60, 61-90, 91-120, 121-180, 181-210, 211-240, 241+) in a pivot table and create a macro to automatically update them each week from data source, and will populate pivot totals in another tab. I have 6 weekly reports that I have to create about 20-25 pivot tables for each, each week.

    I can send a copy of the file, if you would like to take a look at it.

    Can you help me please?

    Reply
63. Macro to increase range of Pivot table says:

    May 2, 2013 at 2:48 pm

    [...] Have a look at the following... How to create a dynamic pivot table and refresh automatically in excel | Get Digital Help - Microsof... [...]

    Reply
64. Torri says:

    May 8, 2013 at 9:50 pm

    Thank you so much you are a lifesaver!

    Reply
65. Torri says:

    May 9, 2013 at 6:10 pm

    The code works while i am in the sheet however once I save, exit, then reopen the sheet - the refresh no longer works... I am unsure what is going on.

    Can you help?

    Reply
    • Oscar says:

      May 10, 2013 at 10:39 am

      Torri,

      Save the file as a *.xlsm file.

66. Kiishore says:

    May 10, 2013 at 5:06 pm

    Hi Oscar,
    I have the following issues. Any suggestions.

    Sheet 1 :
    Grade Male Female
    Primary
    Secondary
    High School
    College
    Gender

    Sheet 2 :
    Col A : Student Id
    Col B: Gender : Here we specify whether the student is male or female
    Col C : Here we specify category to which the student falls : Primary, secondary, high school, college
    Col D : Age : To which age they are in

    Problem :

    In Sheet 1,
    # I should give the count of students who are male and are in
    * primary and they are between 4 to 6 years.
    * Count of Secondary students and between 6 to 10 years ...

    # I should give the count of students who are female and in
    * primary and they are between 4 to 6 years.
    * Count of Secondary students and between 6 to 10 years

    Kindly let me know of any specific formula e that I can use.

    Reply
    • Oscar says:

      May 16, 2013 at 9:58 am

      Kiishore,

      See this post:
      https://www.get-digital-help.com/2013/05/16/count-students/

67. Torri says:

    May 10, 2013 at 5:41 pm

    Thanks so much. It did not work the first few times I saved it that way ... but the third time was the charm and it stuck. Thanks so much.

    Reply
68. Ali Hasnain says:

    June 7, 2013 at 8:36 am

    such a helpful thread.
    i used "OFFSET" instead. much easier and dynamic!

    Reply
69. Oscar says:

    June 7, 2013 at 1:08 pm

    Ali Hasnain,

    How did you get the pivot table to update automatically?

    How can it be easier than an excel defined table?

    Reply
70. Amit says:

    June 17, 2013 at 11:42 am

    Thank you.. it's very helpful for me.

    Reply
71. Paul says:

    June 18, 2013 at 3:51 pm

    I used this simple code. I cannot follow the hyperink i the expanded pivot table. However, the hyperlink i working on the cell that it was created in BEFORE the pivot was collapsed. Any ideas anyone?

    Range("L7").Select
    Selection.End(xlDown).Select
    irow = ActiveCell.Row
    Range("e6:e" & irow).Select
    For Each Cell In Selection
    Cell.Select
    If Cell.Value "" Then
    If Left(Cell.Value, 7) = "https://" Then
    URL = Cell.Value
    Else
    URL = "https://" + Cell.Value
    End If
    ActiveSheet.Hyperlinks.Add anchor:=Cell, _
    Address:=URL, TextToDisplay:=Cell.Value
    End If
    Next

    Reply
72. Luch says:

    July 3, 2013 at 10:00 pm

    Thanks very much! very helpful!!!

    Reply
73. Oscar says:

    July 9, 2013 at 12:49 pm

    Torri, Amit and Luch

    Thanks for commenting!

    Reply
74. Justin says:

    July 9, 2013 at 8:54 pm

    Works great! I have two questions though:

    1. Every time I change data in the 'data' sheet, excel demands confirmation to replace the contents of the destination cell in the sheet containing the pivot tables. Any way to bypass this? I noticed your example does not seek confirmation, so I know it's possible.

    2. Is there any way to protect the sheet with the pivot tables from end users manually changing data within the pivot tables and still allow them to be refreshed?

    Thanks in advance!

    Reply
75. Torri says:

    July 17, 2013 at 4:21 pm

    The Data I am pulling from is:

    ATA Claim Amount # Units
    11/7/2010 $322.89 4
    3/29/2011 $41.36 24 (4 ctns)
    3/4/2011 $20.68 12 (2 ctns)
    3/24/2011 $72.38 42 (7 ctns)
    7/29/2011 $50.79 2
    8/4/2011 $101.58 4
    8/5/2011 $253.96 10 (2 ctns)
    12/9/2011 $76.19 3 units
    12/15/2011 $78.36 4 units
    9/13/2011 $54.42 2
    12/13/2011 $612.23 25
    10/27/2011 $24.49 1
    3/10/2012 $24.49 1
    4/18/2012 $489.78 20 (4 ctns)
    4/16/2012 $124.08 72 (12 ctns)
    5/8/2012 $1,224.45 50 (10 ctns)
    7/1/2012 $59.86 1
    7/15/2012 $489.78 20 (4 ctns)
    7/17/2012 $122.45 5 (1 ctn)
    11/10/2012 $119.72 2 (1ctn)
    11/18/2012 $199.54 10 (2 ctns)
    10/29/2012 $748.82 192 (32 ctn)
    12/6/2012 $25.58 1
    12/9/2012 $25.58 6
    12/3/2012 $126.98 5
    11/4/2012 $38.09 3
    7/31/2012 $122.45 5
    2/17/2013 $54.42 5
    10/26/2012 $20.68 12
    3/23/2013 $982.83 252
    4/8/2013 $51.15 12
    4/3/2013 $39.91 2
    2/25/2013 $54.42 5
    3/27/2013 $23.40 6
    5/20/2013 $25.58 6
    5/7/2013 $49.89 5
    6/10/2013 $25.58 6

    This was my instructions:

    I need them placed into graphs showing the time line from 2010 to present.

    We need to measure three areas on a monthly bases; frequency of events, total amount of claim for the month and unit count for the moth.

    I need it in a three bar graph and line chart with three spate lines for each measurement.

    Reply
76. Toni says:

    July 25, 2013 at 1:42 pm

    Hi Oscar,

    I am looking for a code to refresh my data from ODBC on the first day of the month by 4 a.m. without having to be on the computer to open the excel file.

    Reply
77. Thomas Lauren says:

    August 4, 2013 at 5:21 pm

    I have VBA code to refresh Pivot Table that works wonderfully (except loosing some center text and a few boundary lines.) Now I would like to insert code to Refresh the sheet when it is protected via a macro button placed on the protected pivot table sheet. The data source is sheet 2 (named 'PRev') and the pivot table (named 'PivotTable1A')is on sheet 1 (named 'Scott PT'.

    The code I have for refresh on 'Scott PT' sheet in module1 is as follows and a suggested code for refreshing while protected sheet is on the pivot table sheet 'Scott PR', that I don't understand how to incorporate at the same time,follows after the break after this first code:
    ___________________________

    Code installed that works for refresh follows:

    ____________________________
    Option Explicit
    ------------
    Sub ClassicPlusPivotTableSettings()

    'Last modified and tested 2010-07
    'Much of this is based on a book by Debra Dalgleish
    ' named "Excel Pivot Tables Recipe Book: A Problem-Solution Approach", Chapter 13
    '
    'The following code applies to PivotTable(1) on the active sheet.
    'In summary, this macro:
    ' Applies the Classic PivotTable display, with gridlines and no colors
    ' Ensures that only data that still exists in the data that drives the PivotTable
    ' will appear in the PivotTable dropdown lists
    ' Sets all fields to ascending order with no subtotals
    ' including fields that are not in the Row Labels or Column Labels areas
    ' For the data field(s) in the Values area, changes the setting to Sum, changes the number format, and
    ' if the field in the Values area is named "Amount" ot "Total Amount" it shortens the label
    ' in the PivotTable to "Sum Amt" or "Sum TtlAmt" respectively.

    On Error Resume Next
    Application.ScreenUpdating = False

    Dim pt As PivotTable
    Dim pf As PivotField

    Set pt = ActiveSheet.PivotTables(1)
    pt.ManualUpdate = True

    'This section applies Classic PivotTable settings
    ' and turns off the Contextual Tooltips and the Expand/Collapse buttons
    With pt
    .InGridDropZones = True
    .RowAxisLayout xlTabularRow
    .TableStyle2 = ""
    .DisplayContextTooltips = False
    .ShowDrillIndicators = False
    End With

    'This sets each field in ascending order. It applies this even to fields that are not currently
    ' in the PivotTable.
    For Each pf In pt.PivotFields
    pf.AutoSort xlAscending, pf.Name
    pf.Subtotals(1) = True
    pf.Subtotals(1) = False
    Next pf

    'This command changes the formatting of any field that appears in the Values area
    For Each pf In pt.DataFields
    pf.Function = xlSum
    pf.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
    Next pf

    'These next two sets of statements shorten the description of any field in the Values area,
    ' but only if the field is named "Amount" or "Total Amount"
    With pt.PivotFields("Sum of Amount")
    .Caption = "Sum Amt"
    End With
    With pt.PivotFields("Sum of Total Amount")
    .Caption = "Sum TtlAmt"
    End With

    pt.ManualUpdate = False
    Application.ScreenUpdating = True

    'This ensures that only data that still exists in the data that drives the PivotTable
    ' will appear in the PivotTable dropdown lists
    pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

    End Sub

    ___________________________

    Code for refresh while protection is on and for macro button follows:
    ___________________________

    Press with right mouse button on the sheet tab of the sheet containing the pivottable.
    Select View Code from the context menu.
    Enter or copy/paste the following code into the worksheet module:
    Private Sub Worksheet_Activate()
    Me.PivotTables(1).RefreshTable
    End Sub

    Remark: you have to protect the sheet with UserInterfaceOnly:=True each time the workbook is opened:
    Double-press with left mouse button on the ThisWorkbook node in the project explorer on the left hand side of the Visual Basic Editor.
    Copy/paste the following code into the ThisWorkbook module:
    Private Sub Workbook_Open()
    With Me.Worksheets("Sheet1")
    .AllowPivotTable = True
    .Protect Contents:=True, UserInterfaceOnly:=True, _
    AllowUsingPivotTables:=True
    End With
    End Sub

    where Sheet1 is the name of the sheet containing the pivottable.

    Reply
78. Nobodies says:

    September 7, 2013 at 4:25 pm

    example i have 3 sheet student's attendance.
    i use sheet 1 to check 30 student from class A: attend or absent and sheet 2 to check same 30 student but from class B
    and i use sheet 3 as a record and make option Class : A or B. If i choose Class A, i can see which student whose absent from Class A only. And if i want choose Class B, THE TABEL WILL AUTOMATIC change to Class B and i can see which student whose absent from Class B
    Can u give me what's the tutorial to make that?

    Reply
79. Kannan says:

    October 16, 2013 at 11:27 pm

    can we do the multiple cols. sorting in excel and sample

    Reply
    • Oscar says:

      October 17, 2013 at 8:05 am

      Kannan,

      can you explain in greater detail?

      Perhaps you are looking for this post:
      Sort a table with an array formula

80. Jesse says:

    November 27, 2013 at 3:39 pm

    I added this code to a report that I developed and when other users with Excel 2013 open the file, they get a VBA Runtime error. However, there are two workbooks that it is in and only one of them gives the error. It is acting like the table names or sheet names are not correct but I have double checked everything and also compared the two workbooks. Any thoughts? Would the newer version of excel be causing issues? Thanks for the help and the code!

    Reply
    • Oscar says:

      November 29, 2013 at 1:08 pm

      Jesse,

      It is acting like the table names or sheet names are not correct but I have double checked everything and also compared the two workbooks. Any thoughts?
      No other than the ones you mentioned.

      Would the newer version of excel be causing issues?
      I don´t know, sorry.

81. Pivot table refresh help!!! says:

    December 31, 2013 at 6:37 pm

    […] Auto refresh a pivot table in excel | Get Digital Help - Microsoft Excel resource […]

    Reply
82. Jose says:

    February 14, 2014 at 10:52 am

    Hallo Oscar,

    thank you for your great post! My problem is actually quite different. I would like indeed to refresh automatically my pivot table whenever the source table is changed. The problem is that my source table ist not directly edited, but is a result of several other tables and computations.

    If I use the event "Worksheet_Change" it doesn't really work since Excel doens't trigger the event when the source table is recalculated (other changes are of course correctly handled).

    On the other hand, if I use the event "Worksheet_Calculate" then I can handle the situation but every calculation on the entire sheet will trigger the event and hence the pivot table refresh.

    Is there a way to combine both things, that is, that the pivot table is automatically refreshed when the source table is either changed or recalculated?

    Thank you very much in advanced!

    Jose

    Reply
83. Ayub Bham says:

    March 8, 2014 at 8:40 pm

    Oscar EXCELLENT help for VBA simple routine on AUTO Pivot Table update.

    Reply
84. Alan says:

    April 3, 2014 at 3:26 pm

    Hello Oscar,

    I have a pivot table sourcing data from another pivot table (on the same worksheet); can I get the second pivot table to automatically refresh when the 'master' pivot table is updated?

    Thanks,

    Alan.

    Reply
85. Andrew says:

    April 24, 2014 at 3:04 pm

    Hello Oscar,

    I used your code above and it worked wonderfully. Despite being my first macro I have ever used I even managed to alter the code to get it to control 3 pivot tables at the same time based on one combo box.

    I now have a more difficult problem. I need to consolidate some of my tables into my pivots.... I have 43 different tables, and some times I need to "join" / "consolidate" / "sum up" different tables. So for example if I wanted to create an "entire company consolidated" pivot I would need to join all 43 tables together.

    Reply
    • Oscar says:

      April 25, 2014 at 9:32 am

      Andrew,

      I found this:
      https://blog.laptopmag.com/excel-2013-pivot-table-from-multiples

86. Mr Matt says:

    May 11, 2014 at 8:02 pm

    Oscar, you sir are a steely-eyed missle man!
    Thanks for the great info.

    Reply
87. Alex says:

    August 11, 2014 at 4:32 pm

    Hi Oscar,

    I found this very helpful! So thank you very much for this! However, I have one issue with it. I have multiple pivot tables that I'm trying to switch between and each one of them have different values (Revenue, GOP, NI, etc.).

    So, under your code, each time I switch between them, I have to add the values that I'm looking for. Is there a way to make is so that I don't have to do this each time?

    Any help would be appreciated. Thanks!

    Reply
88. Vivin NL says:

    September 1, 2014 at 10:33 am

    Working fine mate....All the pivottable charts are automatically updated too

    Reply
    • Tapas says:

      November 12, 2014 at 1:53 pm

      Hi
      Oscar

      I do the same exercise as you explain above but when i close and again open the workbook its not working.

      Please help

89. Scott says:

    September 9, 2014 at 3:35 pm

    This was very helpful to me. Thanks for the post.

    Reply
90. narendra says:

    January 27, 2015 at 11:42 am

    this code is good but this is not working for me.i am getting unable to get the pivot table property of the worksheet class.what it means please help me i am new vba.

    Reply
91. Louis says:

    February 24, 2015 at 3:42 pm

    Hi Oscar,
    Your code is very helpful, thank you. But I have a little problem. I have 5 tables with I did one pivot table, and other 4 pivot tables with 5 tables each one, the data is almost the same. I want to change the 5 pivot tables with one drop down list. Is it possible?
    Thanks for your help.

    Reply
92. Katie says:

    March 13, 2015 at 8:50 pm

    Neat trick! Thanks for sharing! I'm pretty new to VBA. How would you get this to work with named data ranges in separate Excel workbooks?

    Reply
    • Oscar says:

      March 16, 2015 at 1:13 pm

      Katie,

      I am not sure this works, change the table names in the drop down list to:

      [Book2]Sheet1!namedrange

      Book2 - Your workbook name
      Sheet1! - Your worksheet name
      namedrange - Your named range

93. Fhats says:

    April 10, 2015 at 4:43 am

    Hi,

    Whenever I refresh a data, the numbers changes its format to dates. but from my source data, the data is still in numbers..

    Reply
94. Chris says:

    April 29, 2015 at 2:18 pm

    Hi Oscar,

    The basic code works for me for refreshing, but i then lose the cell border/highlight when moving around the workbook - ie. i cant see which cell i'm currently in.... any ideas on that one?

    Thanks
    Chris

    Reply
95. Rahul says:

    May 13, 2015 at 12:46 pm

    Thanks it worked

    Reply
96. Bryan says:

    September 17, 2015 at 2:53 pm

    Thank you so much for your help. That was very helpful !

    Reply
97. Rajesh says:

    October 8, 2015 at 3:35 pm

    Hi Oscar,

    the post is really informative and helpful.

    I have multiple pivote tables and multiple source data sheets aswell..

    for example in workbook 1 , i have pivot1 which is generated from the data of sheet1 and pivot2 from sheet2...so on.

    I can write code in specific sheets to refresh respective pivot tables but How can i keep the code in single macro? so that it would be easy to make changes or debug if required.

    thanks in advance

    Reply
98. Arran says:

    October 31, 2015 at 6:19 pm

    Hello Oscar, this is very cool, however I have an added complication, I'm hoping you can help me with. I have other sheets feeding into my data sheet which then feed into the Pivot table. So when a change is made in the data sheet it is via a link not an actual user action in the cell. This results in the pivot table not refreshing. Do you have any suggestions of how to make this work. Otherwise the pivot tables have to be refreshed numerous times to update all of the data.

    Thanks

    Reply
99. Pitambar Singh says:

    November 13, 2015 at 8:25 pm

    Simply use vloopup like

    IF($B$1="A",VLOOKUP(Sheet3!A2,A!$A:$B,2,0),VLOOKUP(Sheet3!A2,B!A:B,2,0))

    Reply
100. KJT says:

     March 18, 2016 at 6:37 pm

     I have a template that I have created to process phone bills. There are may lines of GL coding for particular individuals. The amounts are different every month. I have created a pivot table in the template (both data and pivot table under one tab). I have created a refresh button to refresh the PT. It works in my original tab but when I copy to create a new tab to process a new invoice it does not refresh on the copy. My pivot table is still connected to the original tab. When I enter data in my original tab and refresh on my copy it moves. How do I keep the connection between my source data and pivot table in the copy together and not the original tab?

     Reply
101. dwarkanath says:

     May 11, 2016 at 6:36 am

     hi

     i am trying this but error cames

     Worksheets("PivotTable1").PivotTables("Design").PivotCache.Refresh

     subscript out of range

     Reply
     • dwarkanath says:

       May 11, 2016 at 6:43 am

       also another error occur i.e. Unable to get the pivotTables property of the worksheet class

102. Riyaz says:

     June 29, 2016 at 3:24 am

     Dear sir I have copied the vba code here and it done the magic . Thank u .

     But I have 2 more worksheets in my inventory , i am unable to do the same again for the respective pivot tables

     Kindly help me in this regard

     Thank you

     Reply
103. Felipe Costa Gualberto says:

     July 4, 2016 at 12:56 pm

     Excellent article, Debra!

     Reply
104. Oscar says:

     July 4, 2016 at 1:58 pm

     Thank you, Felipe.

     Reply
     • Felipe Costa Gualberto says:

       July 4, 2016 at 2:31 pm

       lol I'm terrible sorry - I wrote Debra because I was at Debra's site looking for pivot table articles and then I visited yours.
       Excellent article, *OSCAR!

105. Kishor says:

     July 17, 2016 at 1:14 pm

     Thank You So Much...
     Helped me lot!!!!

     Reply
106. Beauxnurr Hertz says:

     July 22, 2016 at 2:09 am

     Thanks for writing this!! It's exactly what I was looking for.
     BNR

     Reply
107. sajid says:

     July 25, 2016 at 9:07 am

     Hi,
     oscar.

     I'm new one in this group and i already read ur post which help me greatly. would u be told me that how to keep equal distance among several columns after refresh. Because when i refresh the pivot sheet then it scrambled how to keep it equal distance between column.

     Reply
108. Jason says:

     July 26, 2016 at 2:07 pm

     When I try to do Worksheet_Change my code doesn't work and even when I try to just refresh a single sheet it still turns into and error/endless loop. Plz help

     Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     For Each ws In ActiveWorkbook.Worksheets
     Call PivRefresh

     Next
     End Sub

     Sub PivRefresh()
     ActiveSheet.PivotTables("PivotTable3").PivotCache.refresh
     End Sub

     Reply
109. Mantej Singh says:

     August 9, 2016 at 5:23 pm

     Hey Thank you so much, really effective and simple solution.

     Reply
110. MikeS says:

     October 14, 2016 at 6:45 pm

     Hi Oscar,
     When I use your original VBA on a pivot table with that works from a range that has values only it works automatically. When I try to use in on a range that contains the results of formulas I have to manually refresh. Is there a way to make it work automatically from source data that contains formulas?
     Thanks,
     MikeS

     Reply
     • Oscar says:

       October 16, 2016 at 11:31 am

       MikeS

       Is there a way to make it work automatically from source data that contains formulas?
       This post shows you how to refresh a pivot table when you activate a sheet, is it something that would work?
       https://www.get-digital-help.com/2011/04/26/how-to-create-a-dynamic-pivot-table-and-refresh-automatically-in-excel-2007/

       If not, your formulas must work with a data from somewhere in your workbook? You could relocate the event code presented in this post to the sheet where your data is located?

111. Rob says:

     October 28, 2016 at 1:09 pm

     This is amazing and saved me a lot of work. THANKS!!!

     Reply
112. prashant says:

     November 18, 2016 at 5:33 pm

     hello Oscar,

     please guide how to change 2 pivot tables at a time.
     the code you shared can change 1 pivot at a time

     Reply
     • Oscar says:

       November 19, 2016 at 10:15 am

       prashant

       Sub ChangeDataSource()
       With ActiveSheet.Shapes(Application.Caller).ControlFormat
           Worksheets("Sheet1").PivotTables("PivotTable1").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
           .List(.Value)
           Worksheets("Sheet2").PivotTables("PivotTable2").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
           .List(.Value)
       End With
       End Sub
       

113. Kannan says:

     November 23, 2016 at 11:48 am

     thanks for the help

     Reply
114. Dan says:

     December 16, 2016 at 1:12 pm

     What an amazingly simple piece of code that makes rolling out multi-user SQL linked reports by pivot table so easy to administer without further training. Thanks for sharing

     Reply
115. Nik says:

     December 25, 2016 at 9:50 am

     Hi, thank you for the trick!

     I have multiple Worksheets each one with one or more Pivots. What if I want to apply the above code to all the pivots and worksheets?

     I was wondering if there is anything "faster" than repeating the command for each table

     Thanks!

     Reply
     • Oscar says:

       December 28, 2016 at 10:55 pm

       Nik

       This code refreshes all pivottables on a sheet if a cell value has changed on the same sheet:

       Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
       Dim pt As PivotTable
       Application.EnableEvents = False
       For Each pt In Sh.PivotTables
           pt.PivotCache.Refresh
       Next pt
       Application.EnableEvents = True
       End Sub
       

       Make sure you put the event code above in "ThisWorkbook" module.
       Note, this will only work as intended if the source data is on the same sheet as the corresponding pivottable.

116. Olut says:

     January 19, 2017 at 1:31 pm

     Hi Oscar,

     Please I am hoping you'll be able to assist me. I designed a template whose data is driven by a Pivot table source. The pivot Table is in turn driven by a source data in the same workbook. I used "IF" formula to pull the data from the pivot table(for example "=if('Sheet1'!B17="","",'Sheet1'!B17). the challenge is that source data are dynamic on a month to month basis and each time I update the the pivot table based on the source data. The 'IF' formula is static and does not change with the template row heading. Please what can i do to make the template(&'IF' formula) row heading to be dynamic with the pivot table when refreshed.

     Just to note that I am not that too vb savvy.

     Please reply.

     Thanks

     Reply
117. Nav says:

     February 19, 2017 at 10:41 pm

     Made my day. Works like a charm

     Reply
     • Oscar says:

       February 20, 2017 at 3:25 pm

       Nav,

       thanks.

118. sumit says:

     May 17, 2017 at 8:19 am

     Hi Oscar,
     I am new to Macro. Please let me know the code through which pivots can get refreshed even when data is added/increased. When I remove some data, it gets updated automatically through the above code but once data is increased it does not gets updated because of range issue. Please help.

     Reply
119. satish says:

     June 29, 2017 at 11:20 am

     can I auto-refresh a linked table similar to a pivot table? both the source table and the linked table are in the same worksheet. any solution with or without a macro is ok. If the source table values change either because o manual entry or because of formula change, the linked table also should change automatically, currently, the refresh happens only if I press with the left mouse button on the refresh button in the table tools design tab

     Reply
120. Create a dynamic named range says:

     March 6, 2018 at 10:41 am

     […] this post I am going to explain the dynamic named range formula in Sam's comment. The formula adds new rows and columns instantly to the named […]

     Reply
121. Steven says:

     September 12, 2018 at 3:27 am

     May I have 1 question? if the pivot table data is linking to another data. For example in the pivot table data, the amount is "=Sheet2!F1". Even if i update the number in Sheet2 F1, the pivot table cannot refresh. Unless I physically press with left mouse button on sheet 1. Do you have a solution?

     Reply
     • Oscar says:

       September 13, 2018 at 8:36 am

       Steven

       Yes, the event handler Worksheet_Change won't be triggered if a cell is linked to another sheet even if the linked cell value changes.

       A simple solution would be to use Worksheet_Change on that worksheet as well.

122. Anoop Jain says:

     March 24, 2020 at 5:45 pm

     Hello,
     I am trying to update 3 pivot tables with the selection from Drop Down menu. I used your code but it is giving me a run-time error 1004 (syntax error)
     on code -
     Worksheets("Sheet2").PivotTables("PivotTable2").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
     .List(.Value)

     Could anyone explain what it does and how to fix the issue with ---> SourceData:=_ ?

     I really appreciate your assistance.

     Regards,
     Anoop

     Reply
     • Oscar says:

       April 3, 2020 at 1:05 pm

       Anoop Jain

       The code uses the selected value from the drop down list to change the Pivot Table's data source.

       Perhaps you are using a value that is not an Excel Table?

123. Leanor says:

     June 24, 2020 at 9:05 am

     Hello,

     I have a question about the hyper-link code.
     If I understand correctly, the code you wrote works for hyper-links to websites.
     What if i have in my worksheet hyper-links to files on my computer?

     Thank you,
     Leanor

     Reply
124. Ester mor perez says:

     July 27, 2021 at 9:00 pm

     Hii, thank u very much for all ur help here,
     I did the macro u wrote above here,
     My links are pic and because I cannot put pic in pivot I use the link with ur macro, Is there a possibility that moving with the mouse the images will appear? Thank u

     Reply
125. Shay Peters says:

     November 4, 2021 at 12:47 am

     I have been working on a pivot table for work. When I pasted in all the hyperlinks, one set posted short links which work with the internal security measures. When I change the link to the full address link, it will not allow employees to access the page.

     I have 2 columns of links. One is working. One is not. I assume bc it's a short link.

     Also if I click on the shortened hyperlink in the original dataset, it works. When I go to the pivot table, it no longer is clickable. Is there a fix for this??

     Reply

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes &lt; and > becomes &gt;

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form