Thread: Pivot table auto update

I am not sure I follow? Why are there only 23 days in a month?

I am not sure if you want the days to coinside with the actually days of the calender, but I think your only way will be with VBA.

Try this,
Open the visual basic editor and paste this into the "ThisWorkbook" Module ,

Private Sub Workbook_Open()

Run "PiviotRefresh"

End Sub

Then go to "Insert" and Insert a new Module , don't insert a classModule, then paste the below code.

Sub PiviotRefresh()

Dim pt As PivotTable

'
  On Error Resume Next
    Range("K1").FormulaR1C1 = "=TODAY( )"
    Range("L1").FormulaR1C1 = "=DAY(RC[-1])"
    Worksheets("Sheet1").Cells(5, 1).Value = Worksheets("Sheet1").Cells(1, 12).Value
   Set pt = ActiveSheet.PivotTables("PivotTable1")

    pt.RefreshTable
    Range("K1:L1").ClearContents

End Sub

Will automatically update your pivot chart to what ever day of the month is. Once you get past 23 however, it will do nothing. Which means that from the 24th of any month your pivot will not update. Tell me if that is what you were after?

JapanDave Many thanks but as JPR kindly said it leaves the original day clicked as well
The answer to why a maximum of 23 days is purely because that is the maximum number of weekdays in a month as I am sure you figured out.

The data that I access is purely Monday through Friday and is stock market related

I have in essence created 3 pivot tables. 1. The Weekday of the Month - (1-23) 2, The Weekday - (Mon thru Frid) 3. The Day of the Year - (1-259 for the year 2011) as I wish to extract a probability scenario based on the seperate outcomes of interrogating the database/pivot tables

Many thanks to JapanDave and JPR73 for your input and help so far

Ok, This will deal with your problem about leaving the previous days number in.

Sub PiviotRefresh()

Dim pt As PivotTable
Dim ws As Worksheet
Dim Day As Variant



        Application.ScreenUpdating = False
            Set ws = Worksheets("Sheet1")
               On Error Resume Next
              
              
                    With ws
                    
                      .Cells(1, 11).FormulaR1C1 = "=TODAY( )"
                      .Cells(1, 12).FormulaR1C1 = "=DAY(RC[-1])"
                      .Cells(5, 1).Value = ws.Cells(1, 12).Value
                    End With
              
         Day = ws.Cells(1, 12).Value
         Day = Day - 1
              
                 
         Set pt = ActiveSheet.PivotTables("PivotTable1")
                            With ActiveSheet.PivotTables("PivotTable1").PivotFields("Day of Month")
                               .PivotItems(Day).Visible = False
                            End With
              
              
                pt.RefreshTable
                Range("K1:L1").ClearContents
        Application.ScreenUpdating = True

End Sub

But you have given a whole new set of variables to deal with. This set of code won't work if you are looking at the table "Just" on weekdays. I am going to have to think about this.

Ok, I figured it out for you. This code is tested and works. Upon opening it deletes the previous days value and adjust for each month and how many days it has in it and put that number in the pivot table accordingly. You are good to go until 2015

Code:

Sub PiviotRefresh()

Dim ws As Worksheet
Dim ws1 As Worksheet
Dim Day As Variant
Dim pt As PivotTable
Dim NDay As Variant

On Error Resume Next
Application.ScreenUpdating = False

Set ws = Worksheets("Sheet2")
Set ws1 = Worksheets("Sheet1")

  
  With ws
  
    If IsNumeric(ws.Cells(1, 6)) Then ws.Cells(3, 6).Value = ws.Cells(1, 6).Value
  
  End With
  
  Day = ws.Cells(2, 6).Value
  NDay = ws.Cells(3, 6)
  
  
   Set pt = ActiveSheet.PivotTables("PivotTable1")
                          With ActiveSheet.PivotTables("PivotTable1").PivotFields("Day of Month")
                              .PivotItems(Day).Visible = False
                              .PivotItems(NDay).visble = True
                          End With
              
              
                pt.RefreshTable
                Range("K1:L1").ClearContents
        Application.ScreenUpdating = True

End Sub

Sub Copy()
 Dim ws As Worksheet
  Set ws = Worksheets("Sheet2")
  
  ws.Cells(2, 6).Value = ws.Cells(1, 6).Value
   

End Sub

Edit: I had to revise the code. You are going to have to wait a day to test it, as it is working with todays date.
If you can't get it to work on your actual file let me know. Cheers

Sorry, that preivious code was bad code. I have re-done the code and this should not give you any problems.

Sub PiviotRefresh()

  Dim pItem As PivotItem
  Dim pvt As PivotTable
  Dim pvtF As PivotField
  Dim sItem As String
  Dim ws As Worksheet

Application.ScreenUpdating = 0

    Set pvt = Worksheets("Sheet1").PivotTables("PivotTable1")
    Set pvtF = pvt.PivotFields("Day of Month")
    Set ws = Sheets("Sheet2")
       sItem = ws.Cells(1, 6).Value
            
                  For Each pItem In pvtF.PivotItems
                    On Error Resume Next
                    pItem.Delete
                    On Error GoTo 0
                  Next pItem
                        On Error GoTo InvalidFilter
                        pvtF.PivotItems(sItem).Visible = True
                        On Error GoTo 0
           
              For Each pItem In pvtF.PivotItems
                If pItem = sItem Then
                  pItem.Visible = True
                Else
                  pItem.Visible = False
                End If
              Next pItem
Application.ScreenUpdating = 1
       Exit Sub


            
InvalidFilter:
              MsgBox ("The filter """ & sItem & """ doesn't exist")


End Sub