+ Reply to Thread
Results 1 to 4 of 4

Macro to Update a Cell Based on Number of Items Selected in Pivot Slicer

  1. #1
    Registered User
    Join Date
    04-26-2013
    Location
    Lexington, KY
    MS-Off Ver
    Excel 2010
    Posts
    21

    Macro to Update a Cell Based on Number of Items Selected in Pivot Slicer

    I am not sure if this is possible, but I am hoping it is. I a beginner with VBA, so I am not sure where to even begin. I have a pivot table with a slicer. The pivot table has several columns to the right of it that are calculations based on the pivot table results to the left. One of the calculated fields is dependent on how many items are selected on one of the slicers. At the moment, I have to manually change a value in a cell to match how many items I have selected in the slicer.

    For example, if I choose only 3 items in the slicer, I have to change cell C4 to 3, so that my calculations are correct. If I choose 1 item, I have to update cell c4 again. This is a report I created to be used by others, and I fear they will forget to update C4 when running through different scenarios with the slicer.

    I am hoping that a simple piece of VBA code will tell cell C4 to update with the number of items that were selected on the slicer, so that the calculated fields will update automatically.

    Any help/suggestions would be greatly appreciated.

    Thanks!

  2. #2
    Registered User
    Join Date
    04-26-2013
    Location
    Lexington, KY
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Macro to Update a Cell Based on Number of Items Selected in Pivot Slicer

    I tried to attach a stripped down version of the file for reference, but I cannot seem to find the attachment button.

  3. #3
    Registered User
    Join Date
    04-26-2013
    Location
    Lexington, KY
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Macro to Update a Cell Based on Number of Items Selected in Pivot Slicer

    I have done some more digging and have been able to figure out how to capture the slicer items that are selected using VBA, and I can msgbox them out, but I just need them to be entered into a range of cells. I am sure this is extremely simple, but I am very inexperienced in VBA.

    Here is the code that is working properly. I just need the code to enter each sItem.Name into a different cell instead of msgboxing it out.


    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    04-26-2013
    Location
    Lexington, KY
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Macro to Update a Cell Based on Number of Items Selected in Pivot Slicer


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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