Filter to show current month

[bcurrey]

I'm working on some VBA code for my company. I'm having the code create a pivot table, and would like for it to filter to show data for current month. Here's the code that I've got but I'm getting an error when it gets to the filtering piece (last line). I'm pretty new to VBA, so it may be something simple. I'm using Excel 2007. Thanks!!

    Sheets("Sheet1").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R37C10", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="Sheet4!R3C1", TableName:="PivotTable4", DefaultVersion _
        :=xlPivotTableVersion12
    Sheets("Sheet4").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Year")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Month")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Date")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Buyer code")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("Number of lines ordered"), _
        "Sum of Number of lines ordered", xlSum
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("Number of lines shipped"), _
        "Sum of Number of lines shipped", xlSum
        
    
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Month").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Month")[COLOR=Red][B].CurrentPage = MonthName(Today)[/B][/COLOR]
    
End Sub

 

[AlphaFrog]

Try using Date instead of Today....
.CurrentPage = MonthName(Date)

 

[bcurrey]

It's still not liking something.

I get a run time error '5' message and when I debug it highlights the last line.

 

[AlphaFrog]

Sorry. Try using this...

.CurrentPage = MonthName(Month(Date))

 

[bcurrey]

Perfect, it works! Thanks!!