Results 1 to 1 of 1

selecting different dates in a pivot table dropdown

Threaded View

  1. #1
    Forum Contributor
    Join Date
    03-31-2017
    Location
    usa
    MS-Off Ver
    2013
    Posts
    104

    selecting different dates in a pivot table dropdown

    hi, I am trying to use code to select dates between a range of dates using the code below. It selects the dates with the dd/mm/yyy format but won't select the ones with just the yyyy format. code is below and file is attached.

    Sub PTSelect1()
       
        Dim PvtTbl1 As PivotTable
        Dim pf As PivotField
        Dim pi As PivotItem
        
        Dim datecriteria1, datecriteria2 As String
            
        datecriteria1 = "1/1/2001"
        datecriteria2 = "9/1/2017"
             
        Application.ScreenUpdating = False
        
        Set PvtTbl = Worksheets("orders").PivotTables("PivotTable1")
        Set pf = PvtTbl.PivotFields("Need By Period")
        
        ' get run-time 1004 error here so leaving out
        ' pf.NumberFormat = "mm/dd/yyyy"
    
        pf.ClearAllFilters      ' clear filters. needs ELSE statement code
        
        ' not picking up just the years
        For Each pi In pf.PivotItems
            If CDate(pi.Value) >= datecriteria1 And CDate(pi.Value) <= datecriteria2 Then
                On Error Resume Next
                pi.Visible = True
            'include ELSE section if you want to clear all filters
            Else
                pi.Visible = False
            End If
        Next
      
        Application.ScreenUpdating = True
        
    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Pivot Table - Selecting only Specific Columns of Data to Pivot
    By newqueen in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 01-17-2019, 04:49 PM
  2. [SOLVED] Filter dates in pivot table by entering date not selecting from drop down
    By bbeards in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 08-03-2017, 01:08 PM
  3. Replies: 3
    Last Post: 03-03-2017, 12:32 PM
  4. [SOLVED] Selecting a table based on two dropdown lists.
    By nudiekap in forum Excel General
    Replies: 7
    Last Post: 02-18-2014, 11:40 AM
  5. Replies: 0
    Last Post: 07-25-2012, 11:22 AM
  6. Faster code for selecting dates from conditions, in pivot table
    By Cedicon in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-26-2011, 07:15 PM
  7. Replies: 0
    Last Post: 03-22-2009, 07:17 PM

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