Consulting

Results 1 to 7 of 7

Thread: Need Assistance with Pivot Table Change

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Newbie
    Joined
    Oct 2018
    Posts
    4
    Location

    Question Need Assistance with Pivot Table Change

    I am trying to automate date selection changes in a pivot table. Below is the code that I cannot seem to get to work. I have hard-coded the value for my variable "MYString" just for the purposes of testing. I have added some notes in the code to clarify. I am using Excel 2013.



    Sub MOM_2()
    
    
    
    
    Dim MYString As String
    
    
    MYString = " Array("""", """", ""[Report Date].[Date].[Day].&[2018-10-01T00:00:00]"", ""[Report Date].[Date].[Day].&[2018-10-02T00:00:00]"", ""[Report Date].[Date].[Day].&[2018-09-01T00:00:00]"", ""[Report Date].[Date].[Day].&[2018-09-02T00:00:00]"")"
    Debug.Print MYString
    
    
        ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields( _
            "[Report Date].[Date].[Year]").VisibleItemsList = Array("")
        
        ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields( _
            "[Report Date].[Date].[Quarter]").VisibleItemsList = Array("")
        
        ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields( _
            "[Report Date].[Date].[Month]").VisibleItemsList = Array("")
    
        'If uncommented, the line below works.
        'ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields("[Report Date].[Date].[Day]").VisibleItemsList = Array("", "", "[Report Date].[Date].[Day].&[2018-10-01T00:00:00]", "[Report Date].[Date].[Day].&[2018-10-02T00:00:00]", "[Report Date].[Date].[Day].&[2018-09-01T00:00:00]", "[Report Date].[Date].[Day].&[2018-09-02T00:00:00]")
        
    'This is the line where I use my variable.  When I check the value of the variable from debug.print it matches the line above exactly from the word Array to the end.  
    'In fact, if I copy the value of "MYString" from the immediate window and paste it over the word MyString it runs fine.
    
       ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields("[Report Date].[Date].[Day]").VisibleItemsList = MYString
    
    End Sub

    Thanks in advance. I've been wracking my brain on this one and it is probably a simple fix.
    Last edited by BBlevins; 10-12-2018 at 08:33 AM.

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
  •