Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Nov 2008
    Location
    England, Yate, Bristol
    Posts
    51
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Excel VBA Pivot Table Outline view for Date filters

    I have a pivottable with a date field. I want to be able to filter the date field using the full Date filter options.

    I have changed the view to Outline and Collapsed the Date field and the options show the full Date Filter options.

    When I record the action in a macro and run it, I get the correct view, but not the full Date Filter options.

    I suspect it is a version problem. I am running Excel 2013 and the Pivot Table code specifies version 10 to make the charts compatible with 2010.

    The date field is called "Start Date"

    The code for Outline view is:

    ActiveSheet.PivotTables("PivotTable1").RowAxisLayo ut xlOutlineRow
    Range("B12").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields ("ProvType").ShowDetail = False

    The code for version is:

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
    "student data!R1C1:R" & LastRow & "C18", Version:=xlPivotTableVersion10). _
    CreatePivotTable TableDestination:="analysis!R1C1", TableName:= _
    "PivotTable1", DefaultVersion:=xlPivotTableVersion10
    Sheets("analysis").Select

    Can anyone point me to the correct code please?

    Peter

  2. #2
    Star Lounger
    Join Date
    Nov 2008
    Location
    England, Yate, Bristol
    Posts
    51
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Sorry to waste anyone's time with this. The solution is to change the version number to 12.

    A major obstacle for me was to find a machine with 2007 or 2010 on as mine has 2013.

    Anyway request post closed with thanks.

Tags for this Thread

Posting Permissions

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