+ Reply to Thread
Results 1 to 19 of 19

Why isn't this code working? To run a macro when Pivot Table changes

Hybrid View

  1. #1
    Registered User
    Join Date
    09-14-2011
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    66

    Why isn't this code working? To run a macro when Pivot Table changes

    I got this code which I want to run each time my OLAP pivot table (PT) changes, so that the formulae in columns K-L will re-size to match the size of the PT. I created a named range called ReceiptAnalysis and I used ="OFFSET(ReceiptAnalysis!$A$5,0,0,COUNTA(ReceiptAnalysis!$A:$A),10)" to make the range change depending on the size of the PT. I'm wondering if it's because it's an OLAP PT instead of a normal one and if this is the case, is there a different method I can use to make it run when the PT is updated?

    PHP Code: 
    Private Sub Worksheet_Change(ByVal Target As Range)
        
    Dim iSect As Range
        Set iSect 
    Application.Intersect(TargetRange("ReceiptAnalysis"))
        If 
    Not iSect Is Nothing Then
            Call PivotMacro
        End 
    If
    End Sub
     
    Sub PivotMacro
    ()
        
    Dim ReceiptAnalysisSheet As Worksheet

        Set ReceiptAnalysisSheet 
    Worksheets("ReceiptAnalysis")
        
            
    With ReceiptAnalysisSheet
        
            Lrows 
    = .Cells(.Rows.Count"K").End(xlUp).Row
        
            
    .Range(.Cells(711), .Cells(Lrows"L")).ClearContents
        
            End With
        
            With ReceiptAnalysisSheet
        
            
    .Range("K6:L6").AutoFill Destination:=.Range("K6:L" & .Range("J" & .Rows.Count).End(xlUp).Row)
        
            
    End With
    End Sub 

  2. #2
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Why isn't this code working? To run a macro when Pivot Table changes

    Why using Worksheet_Change event and not use some of the worksheet events connected with PivotTable?
    If you are pleased with a member's answer then use the Star icon to rate it.

  3. #3
    Registered User
    Join Date
    09-14-2011
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    66

    Re: Why isn't this code working? To run a macro when Pivot Table changes

    Sorry I'm not sure I understand what you mean. I am new to VBA so I found that first bit of code on a forum somewhere. The Sub PivotMacro bit is what I did and then I added it into the code I found

  4. #4
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Why isn't this code working? To run a macro when Pivot Table changes

    Instead of Worksheet_Change event you can use code like this

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
        If Target.Name = "YourPivotTableName" Then ' Change the name to match the name of your Pivot table
            ' some code here e.g.
            Call PivotMacro
        End If
    End Sub
    Here is reference to MSDN:
    https://msdn.microsoft.com/en-us/lib.../ff822105.aspx

  5. #5
    Registered User
    Join Date
    09-14-2011
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    66

    Re: Why isn't this code working? To run a macro when Pivot Table changes

    I tried it and it didn't work. I copied your code exactly, and I changed to the name of my PivotTable but it doesn't work!

  6. #6
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Why isn't this code working? To run a macro when Pivot Table changes

    what does it mean that it didn't work? Where did you place the code (it should be in the sheet where the PivotTable is). When PT is updated does the code execute? To check this put a break point and update the table or add code to display msg (i.e. to be sure that PivotTableUpdate event is fired.

  7. #7
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Why isn't this code working? To run a macro when Pivot Table changes

    Hi,
    find attached sample with mock PivotTable and implementation of Worksheet_PivotTableUpdate event.
    I've included 5 MsgBox to display the address of different parts of the PivotTable as my understanding is that you want to update the reference of a named range.
    Change some values in the table on sheet1 and then refresh the PT on sheet2. Note that if you add rows, they will not be automatically reflected in the PT. For the example just change values in the existing table.
    If you want you may want to explore also different events if someone will serve you better:

    Worksheet_PivotTableAfterValueChange Event
    Worksheet_PivotTableBeforeAllocateChanges Event
    Worksheet_PivotTableBeforeCommitChanges Event
    Worksheet_PivotTableBeforeDiscardChanges Event
    Worksheet_PivotTableChangeSync Event
    Worksheet_PivotTableUpdate Event -> the one which I implemented.
    Attached Files Attached Files
    Last edited by buran; 05-29-2015 at 02:45 AM.

  8. #8
    Registered User
    Join Date
    09-14-2011
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    66

    Re: Why isn't this code working? To run a macro when Pivot Table changes

    I think it must be because my PT is from an OLAP cube, I replictaed the data by copying/pasting values into another workbook and inserted a pivot table based on that. Copied your code and it worked.

    I then modified your message boxes to my code and that worked, but when I copied the code over to my original report with the OLAP PT it gave me a weird error message:
    Capture.PNG

  9. #9
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Why isn't this code working? To run a macro when Pivot Table changes

    I just checked with my Excel 2007 VBA Programmer's Reference ant there IS an example with WorksheetPivoTableUpdate event when working with PivotTable from OLAP source. I just wanted to be sure that this event is applicable. So it should work... Obviously there is something we are missing here

  10. #10
    Registered User
    Join Date
    09-14-2011
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    66

    Re: Why isn't this code working? To run a macro when Pivot Table changes

    Well if you don't know then there's absolutely no hope for me

  11. #11
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Why isn't this code working? To run a macro when Pivot Table changes

    Hm, I have no idea what is that or what it could cause it. If someone can help... I don't have a clue

  12. #12
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Why isn't this code working? To run a macro when Pivot Table changes

    Let's hope someone else could jump in and help.

  13. #13
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,968

    Re: Why isn't this code working? To run a macro when Pivot Table changes

    Let's start from the beginning. If you use:
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
        Msgbox "Running"
    End Sub
    and refresh your OLAP pivot, do you see the message "Running", or the error message?
    Everyone who confuses correlation and causation ends up dead.

  14. #14
    Registered User
    Join Date
    09-14-2011
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    66

    Re: Why isn't this code working? To run a macro when Pivot Table changes

    I think IT were running some updates on the cube server which was causing the error message, so forget that part!

    I did your code and refreshed the table, no message box appeared.

  15. #15
    Registered User
    Join Date
    09-14-2011
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    66

    Re: Why isn't this code working? To run a macro when Pivot Table changes

    I think IT were running some updates on the cube server which was causing the error message, so forget that part!

    I did your code and refreshed the table, no message box appeared.

  16. #16
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,968

    Re: Why isn't this code working? To run a macro when Pivot Table changes

    And you definitely put it in the code module of the worksheet containing the pivot table? There shouldn't be any difference between OLAP and regular pivot tables in this respect - refreshing either should trigger this event.

  17. #17
    Registered User
    Join Date
    09-14-2011
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    66

    Re: Why isn't this code working? To run a macro when Pivot Table changes

    I'm pretty sure I did. In the project explorer pane on the left i clicked on the sheet and then insert module, is that the right way to do it? There is only one sheet in the workbook anyway

    Capture.PNG

  18. #18
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,968

    Re: Why isn't this code working? To run a macro when Pivot Table changes

    No - just double click the sheet name (or from the main Excel Window, right click the sheet tab and then choose View Code. Your code currently is in a normal module, not the worksheet module.

  19. #19
    Registered User
    Join Date
    09-14-2011
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    66

    Re: Why isn't this code working? To run a macro when Pivot Table changes

    It worked!!!! Thank you everyone for your help

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Sorting a table with a macro - code not working
    By MikeRoot in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-03-2014, 04:40 PM
  2. [SOLVED] Pivot table macro not working and I'm at a lose as to why
    By punter in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-03-2014, 11:13 AM
  3. Pivot Table Filter Macro Not working
    By sm9748 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-23-2013, 03:43 AM
  4. Updating pivot table filters through a macro has stopped working, help please
    By JasperTata in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-21-2012, 09:53 AM
  5. Macro for Pivot table not working properly
    By oakman in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-27-2007, 09:46 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1