VBA generated Pivot table will not group based on a date field

Hi Experts,

I have a procedure, that generates a pivottable, and groups the date into month and year. This has worked fine for month after month, but suddenly it stops working.

Unfortunately the range A22 (since more records were added to the recordset) is no longer a datefield, and my grouping goes wrong.

Sub CreatePivotTable()
'---------------------------------------------------------------------------------------------------------------------
' Procedure   : CreatePivotTable
' Author      : Jørgen Wulff Rasmussen, PwC Phone +45 61 65 25 16 or +45 23 73 20 09
' Date        : 20-11-2012
' Purpose     : This Macro Creates a Pivot Table and formats the cells to fit into the format, that optimizes reporting
'               of actual hours for month that has not been finalised (primarily actual month)
'---------------------------------------------------------------------------------------------------------------------
Dim wsData          As Worksheet
Dim pRange          As Range
Dim nLastRow        As Integer
Dim nLastColumn     As Integer
    
Set wsData = ActiveWorkbook.Worksheets("Tidsforbrug")
nLastRow = wsData.Cells(Rows.Count, 1).End(xlUp).Row
nLastColumn = wsData.Cells(1, Columns.Count).End(xlToLeft).Column
Set pRange = wsData.Cells(1, 1).Resize(nLastRow, nLastColumn)

    Range("a1").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        pRange, Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="PivotTable!R3C1", TableName:="Pivottabel1", DefaultVersion:= _
        xlPivotTableVersion14
    Sheets("PivotTable").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("Pivottabel1").PivotFields("Arb.Ordre (T)")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Pivottabel1").PivotFields("Resnr. (T)")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("Pivottabel1").PivotFields("Bilagsdato2")
        .Orientation = xlRowField
        .Position = 3
    End With
    Range("A22").Select
    Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
        True, True, False, True)
    ActiveSheet.PivotTables("Pivottabel1").PivotFields("Bilagsdato2").Orientation _
        = xlHidden
        
    With ActiveSheet.PivotTables("Pivottabel1").PivotFields("Konsulenttype")
        .Orientation = xlPageField
        .Position = 1
        .CurrentPage = "E"
    End With
        With ActiveSheet.PivotTables("Pivottabel1").PivotFields("År")
        .Orientation = xlPageField
        .Position = 2
        .CurrentPage = "2013"
    End With
    With ActiveSheet.PivotTables("Pivottabel1").PivotFields("Måneder")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Pivottabel1").PivotFields("Uge")
        .Orientation = xlColumnField
        .Position = 2
    End With
    ActiveSheet.PivotTables("Pivottabel1").AddDataField ActiveSheet.PivotTables( _
        "Pivottabel1").PivotFields("Timer"), "Sum af Timer", xlSum
    With ActiveSheet.PivotTables("Pivottabel1").PivotFields("Måneder")
        .PivotItems("Jan").Visible = False
        .PivotItems("Mar").Visible = False
        .PivotItems("Apr").Visible = False
        .PivotItems("May").Visible = False
        .PivotItems("Jun").Visible = False
        .PivotItems("Jul").Visible = False
        .PivotItems("Aug").Visible = False
        .PivotItems("Sep").Visible = False
        .PivotItems("Oct").Visible = False
        .PivotItems("Nov").Visible = False
        .PivotItems("Dec").Visible = False

    End With
    ActiveSheet.PivotTables("Pivottabel1").PivotFields("Arb.ordre (T)").ShowDetail = False
          
End Sub

Select allOpen in new window

Does any of you have the code, so I will always refer to the datefield called "Bilagsdato2", and thereby being able to group on month and year as well?