Macro pivot table automatically updates date and name

Question

I am trying to create a macro that can pull daily reports into a pivot table. I am extremely new to VB but working it out slowly. I would like the activeworkbook to be the one that I'm using rather than a predefined name (here it is report (40). ).

Then I would like it to take the whole range (which increases everyday) and make a pivot table out of it.

Lastly, I would like the data to only use today's date.

Any help?

Sub DailyS()
'
' DailyS Macro
'
' Keyboard Shortcut: Ctrl+d
'
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, **SourceData:= _
        "report (40)!R1C1:R324C129**", Version:=xlPivotTableVersion15). _
        CreatePivotTable TableDestination:="**Sheet1!R3C1**", TableName:="PivotTable13" _
        , DefaultVersion:=xlPivotTableVersion15

Sheets("Sheet1").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable13").PivotFields("Date")
        .Orientation = xlRowField
        .Position = 1
    End With
With ActiveSheet.PivotTables("PivotTable13").PivotFields("UserP")
    .Orientation = xlRowField
    .Position = 2
End With

With ActiveSheet.PivotTables("PivotTable13").PivotFields(**"Date"**)
    .Orientation = xlPageField
    .Position = 1
End With

ActiveSheet.PivotTables("PivotTable13").PivotFields("Date").CurrentPage = _
    "(All)"
With ActiveSheet.PivotTables("PivotTable13").PivotFields("Date")
    .PivotItems("10/1/2013").Visible = False
    .PivotItems("10/2/2013").Visible = False
    .PivotItems("10/3/2013").Visible = False
    .PivotItems("10/4/2013").Visible = False
    .PivotItems("10/5/2013").Visible = False
    .PivotItems("10/6/2013").Visible = False
    .PivotItems("10/7/2013").Visible = False
    .PivotItems("10/8/2013").Visible = False
    .PivotItems("10/9/2013").Visible = False
    .PivotItems("10/10/2013").Visible = False
    .PivotItems("10/11/2013").Visible = False
    .PivotItems("10/12/2013").Visible = False
    .PivotItems("10/13/2013").Visible = False
    .PivotItems("10/14/2013").Visible = False
    .PivotItems("10/15/2013").Visible = False
    .PivotItems("10/16/2013").Visible = False
    .PivotItems("10/17/2013").Visible = False
    .PivotItems("10/18/2013").Visible = False
    .PivotItems("10/19/2013").Visible = False
    .PivotItems("10/20/2013").Visible = False
    .PivotItems("10/21/2013").Visible = False
    .PivotItems("10/22/2013").Visible = False
End With

L42 L42 · Accepted Answer · 2013-10-24T10:04:11

I'm only second guessing what you want.
But to help you get started, here's some code that might be of help.

Code to update pivot source and select specified date in Pivot.

Dim pt as PivotTable, pt_item as PivotItem
Dim date_stamp as Date, source_data as string
Dim ws as WorkSheet
Dim Last_row as Long

Set ws = Activeworkbook.Sheets(1) 'considering Sheet1 has the pivot.
Last_row = ws.Range("A" & ws.Rows.Count).End(xlup).Row    

source_data = ws.Name & "!" & ws.Range("A1:E" & Last_row).Address(ReferenceStyle:=xlR1C1)
'source_data contains your dynamic range address
date_stamp = Format(Now(), "m/d/yyyy") 'this contains the date now, format as you wish

For Each pt in ws.PivotTables
If pt.Name = "PivotTable13" then
    pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
            SourceData:=source_data) 'this refresh the data source
    'this For Loop Selects the Current Date, just change the field name
    For Each pt_item In pt.PivotFields("Field_Name").PivotItems
        Select Case pt_item.Name
        Case date_stamp
            pt_item.Visible = True                
        Case Else            
            pt_item.Visible = False            
        End Select            
    Next pt_item
end if
Next pt

Not exactly what you need but I hope this gets you started.
I only assumed that your data is contained in Columns A:E.

Macro pivot table automatically updates date and name

1 Answers