Consulting

Results 1 to 1 of 1

Thread: Macro to Display Certain Items of a Pivot Field

  1. #1
    VBAX Regular
    Joined
    Jan 2011
    Location
    Virginia
    Posts
    10
    Location

    Macro to Display Certain Items of a Pivot Field

    So I had a previous post about how I was trying to prompt a user to input a value and then based upon that input filter a pivot field on that input. And managed to come up with the following code:

    [VBA]
    Public Sub ShowItem()
    'Declare local varaiables
    Dim MonthSDD As String
    Dim ItemFound As Boolean, pvtItm

    'Get the text entered by the user.

    MonthSDD = ActiveSheet.Range("M2").Value
    ItemFound = False

    'Make every item in the pivottable visible.

    For Each pvtItm In ActiveSheet. _
    PivotTables("PivotTable7"). _
    PivotFields("Month (Standardized Delivery Date)").PivotItems
    pvtItm.Visible = True

    'If a pivottable item matches the user's text, set
    'ItemFound to TRUE (was previously FALSE).

    If pvtItm.Value = MonthSDD Then
    ItemFound = True
    End If
    Next pvtItm

    'If no item in the pivottable matches the user's text,
    'display an error message and quit.

    If ItemFound = False Then
    MsgBox SelItem & " Not a Valid Month in Data Range."
    Exit Sub
    End If

    'Hide every item in the pivottable that does not
    'match the user's text.

    For Each pvtItm In ActiveSheet. _
    PivotTables("PivotTable7"). _
    PivotFields("Month (Standardized Delivery Date)").PivotItems
    If pvtItm.Value = MonthSDD Then
    pvtItm.Visible = True
    Else
    pvtItm.Visible = False
    End If
    Next pvtItm

    End Sub
    [/VBA]

    But, now my question is how do I ask a user for an input 'RANGE (by month/year)' not just a single value, and then once 'RANGE (by month/year)' is entered only show that date range for the column field?

    So....

    Is it possible to code a macro to prompt a user for say, a date range like:

    Date Range is: "Nov-09 to Nov-10"

    and then have the pivot table field sort based upon that specified input?

    In the attached file I need to prompt the user for the a specified date range ("Monthly Standardized Date") which is the column field on my pivot and then have the pivot generate based upon that date range entered.

    Also, in most cases the seleciton will be a 12mos - 15mos range.....

    Thanks in Advance Friends!
    Attached Files Attached Files

Posting Permissions

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