none
Refresh slicer using Macro RRS feed

  • Question

  • I recorded a macro in Excel 2010 which is below.  My intention is to print a series of department numbers using the slicer that is connected to 3 pivot tables, it worked for the first time but it does not refresh when I run it again and the report only gives me "Get Data........", there is no refresh of the slicer.   In other words I would like to mimic a user clicking the slicer in the macro to print.

    Any help will be great =, ===

     =======================================  

      ActiveWorkbook.SlicerCaches("Slicer_Dept_and_Segment1").VisibleSlicerItemsList _
            = Array( _
           "[Combined Years Dataset].[Dept and Segment].&[0704403 - 00123000]")
        Sheets(Array("PIVOT_cube", "Detail-Summary", "Detail Sheet 2")).Select
        Sheets("PIVOT_cube").Activate
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
            IgnorePrintAreas:=False
        Sheets("PIVOT_cube").Select
        ActiveWorkbook.SlicerCaches("Slicer_Dept_and_Segment1").VisibleSlicerItemsList _
            = Array( _
            "[Combined Years Dataset].[Dept and Segment].&[0704404 - 01450000]")
        Sheets(Array("PIVOT_cube", "Detail-Summary", "Detail Sheet 2")).Select
        Sheets("PIVOT_cube").Activate
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
            IgnorePrintAreas:=False
        Sheets("PIVOT_cube").Select
        ActiveWorkbook.SlicerCaches("Slicer_Dept_and_Segment1").VisibleSlicerItemsList _
            = Array( _
            "[Combined Years Dataset].[Dept and Segment].&[0704405 - 00666000]")
        Sheets(Array("PIVOT_cube", "Detail-Summary", "Detail Sheet 2")).Select
        Sheets("PIVOT_cube").Activate

    End Sub

        Thanks

    K


    Kofi

    Sunday, September 20, 2015 11:35 PM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi Zhang
    TechNet Community Support


    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Monday, September 21, 2015 1:23 AM
  • Thanks so much for your help.

    Kofi

    Monday, September 21, 2015 2:02 AM
  • We are doing the research about your problem. There might be some delay about the response. Appreciate your patience.
    Friday, September 25, 2015 9:50 AM
  • Hi Kofi,

    Based on the code, you were using the OLAP data source in the Excel and filter the data. Based on my understanding, the OLAP server performs calculations to summarize the data. Only the summarized data is returned to Excel, on an as-needed basis.

    I suggest that you debug the code to see whether the pivot table was update after you reset the slicer. If this issue the root cause for this issue, we can also add the code to track the data change in the excel then print out the worksheet.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, October 9, 2015 8:11 AM
    Moderator
  • No the issue was not the update of the pivot table.  All I need is some kind of loop to pick up the next clicked on slicer and run it thru to show the report on  the screen.  It works when I don't use the slicer but I would rather use the slicer.

    Thanks


    Kofi

    Monday, October 12, 2015 1:40 PM