Setting Current Date Data in Pivot Table

[onemoremile]

Hi All:

I apologize in advance if this is in the wrong forum. I am relatively new to VB (know enough to be dangerous).

My Situation: I am working with data cubes pulled into pivot tables. I have to create an extended worksheet that pulls in
KPIs for employees (which I have already done). Now, I have been informed that for each KPI (more than 10), the powers-that-
be want to view it for Yesterday's date, Week total, and Month total. I tried to create a macro to display the correct current date
range that displays by a button click (see below).

When I recorded this macro, I realized that the pivot table it generated had everything (as far as date ranges) that I needed to input for the KPIs in my
massive spreadsheet. Is there a way to tweak this code to update the pivot table surrounding the current date? For example: Today is 10-31-2013 so on clicking the button, my pivot table changes to display just yesterday's totals, the totals for the week (Monday 10-27 thru Sunday 11-3-2013) and totals for the month/period. And magically next week sometime, let's say Tuesday 11-5-2013 I click the button and it defaults surrounding that date (where yesterday=11-4-2013, week=11-4-2013 through 11-10-2013, and month=the current month's totals). Ultimately, if this can be done, I would like to transfer this working macro to other pivot tables/cube connections. And maybe somehow incorporate this into an Access userform I'm creating to query/retrieve reports from my massive spreadsheet.

Please use small words and hand puppets if necessary. I really am overwhelmed at this coding. Please, let me know where I am unclear in my description.

Thank you for any help!

My code:

Sub testweekdate()
'
' testweekdate Macro
' test what the syntax is for week date
'

'
    ActiveSheet.PivotTables("PivotTable2").CubeFields( _
        "[Company Name Date].[Company Name Date]").Orientation = xlHidden
    ActiveSheet.PivotTables("PivotTable2").PivotFields( _
        "[Company Name Date].[Company Name Date].[Year]").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable2").CubeFields( _
        "[Company Name Date].[Company Name Date]").CreatePivotFields
    ActiveSheet.PivotTables("PivotTable2").PivotFields( _
        "[Company Name Date].[Company Name Date].[Year]").VisibleItemsList = Array("")
    ActiveSheet.PivotTables("PivotTable2").PivotFields( _
        "[Company Name Date].[Company Name Date].[Qtr]").VisibleItemsList = Array("")
    ActiveSheet.PivotTables("PivotTable2").PivotFields( _
        "[Company Name Date].[Company Name Date].[Period]").VisibleItemsList = Array("")
    ActiveSheet.PivotTables("PivotTable2").PivotFields( _
        "[Company Name Date].[Company Name Date].[Week]").VisibleItemsList = Array( _
        "[Company Name Date].[Company Name Date].[Week].&[2013]&[10]&[5]")
    ActiveSheet.PivotTables("PivotTable2").PivotFields( _
        "[Company Name Date].[Company Name Date].[Date]").VisibleItemsList = Array("")
    With ActiveSheet.PivotTables("PivotTable2").CubeFields( _
        "[Company Name Date].[Company Name Date]")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable2").CubeFields( _
        "[Company Name Date].[Company Name Date]")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable2").CubeFields( _
        "[Company Name Date].[Company Name Date]")
        .Orientation = xlColumnField
        .Position = 1
    End With
    Range("B4").Select
    ActiveSheet.PivotTables("PivotTable2").PivotFields( _
        "[Company Name Date].[Company Name Date].[Year]").PivotItems( _
        "[Company Name Date].[Company Name Date].[Year].&[2013]").DrilledDown = True
    Range("B5").Select
    ActiveSheet.PivotTables("PivotTable2").PivotFields( _
        "[Company Name Date].[Company Name Date].[Qtr]").PivotItems( _
        "[Company Name Date].[Company Name Date].[Qtr].&[2013]&[4]").DrilledDown = True
    Range("B6").Select
    ActiveSheet.PivotTables("PivotTable2").PivotFields( _
        "[Company Name Date].[Company Name Date].[Period]").PivotItems( _
        "[Company Name Date].[Company Name Date].[Period].&[2013]&[10]").DrilledDown = True
    Range("B7").Select
    ActiveSheet.PivotTables("PivotTable2").PivotFields( _
        "[Company Name Date].[Company Name Date].[Week]").PivotItems( _
        "[Company Name Date].[Company Name Date].[Week].&[2013]&[10]&[5]").DrilledDown = True
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1

[HanneSThEGreaT]

Use CODE tags please!

[Nightwalker83]

Which version of VB are you using?

[onemoremile]

@Hannes: Sorry about that. Fixed.
@Nightwalker: 6.5