Sub Macro2()
'
' Macro2 Macro
'

'
    
    Dim year As Integer
    Dim month As Integer
    Dim day As Integer
    Dim x As String
    
    Sheets("s").Select
    Range("A1").Select
    'MsgBox (Sheets("s").Range("A1").Value)
    Cells(1, 1) = "= Today() - 1"
    day = Left(Cells(1, 1), 2)
    month = Mid(Cells(1, 1), 4, 2)
    year = Mid(Cells(1, 1), 7, 4)
    x = "2012 Q3"
    
    Application.CutCopyMode = False
    '--------------------------------
    Sheets("k").Select
    ActiveSheet.PivotTables("ARENA - MS Office PivotTable 12.0").PivotFields( _
        "[Date].[Calendar Date]").AddPageItem _
        "[Date].[Calendar Date].[Day].[" & year & "].[" & x & "].[" & month & "].[" & day & "]", True
        'Time].[All Time].[    "].[" & x &
        '"[Date].[Calendar Date].[Day].&[" & year & "].[" & month & "].[" & day & "]", True
    '-----------------------------------------
    
    Range("B5:E5").Select
    Selection.Copy
    Sheets("s").Select
    Range("C5").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub