Group by Week

By default Excel starts the week based on the first date found in the data. This means that the default week would run from Tuesday January 1, 2006 through Monday December 31, 2007. You can override this by changing the Start parameter from true to an actual date. Use the WeekDay function to determine how many days to adjust the start date.

There is one limitation to grouping by week. When you group by week, you cannot also group by any other measure. It is not valid to group by week and quarter.

Listing 5 creates the report shown in Figure 17.

Listing 5. The Code Used to Group by Week Must Figure Out the Monday Nearest the Start ofYour Data

Sub ReportByWeek()
    Dim WSD As Worksheet
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PRange As Range
    Dim FinalRow As Long

    Set WSD = Worksheets("PivotTable")
    Dim WSR As Worksheet

    ' Delete any prior pivot tables
    For Each PT In WSD.PivotTables
        PT.TableRange2.Clear
    Next PT

    ' Define input area and set up a Pivot Cache
    FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
    FinalCol = WSD.Cells(1, Application.Columns.Count). _
        End(xlToLeft).Column
    Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
        xlDatabase, SourceData:=PRange.Address)

    ' Create the Pivot Table from the Pivot Cache
    Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
        Cells(2, FinalCol + 2), TableName:="PivotTable1")

    ' Turn off updating while building the table
    PT.ManualUpdate = True

    ' Set up the row fields
    PT.AddFields RowFields:="In Balance Date", ColumnFields:="Region"

    ' Set up the data fields
    With PT.PivotFields("Revenue")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 1
        .NumberFormat = "#,##0"
    End With

    ' Ensure that you get zeroes instead of blanks in the data area
    PT.NullString = "0"

    ' Calc the pivot table to allow the date label to be drawn
    PT.ManualUpdate = False
    PT.ManualUpdate = True

    ' Group Date by Week.
    'Figure out the first Monday before the minimum date
    FirstDate = PT.PivotFields("In Balance Date").LabelRange. _
        Offset(1, 0).Value
    WhichDay = Weekday(FirstDate, 3)
    StartDate = FirstDate - WhichDay
    PT.PivotFields("In Balance Date").LabelRange.Group _
        Start:=StartDate, End:=True, By:=7, _
        Periods:=Array(False, False, False, True, False, False, False)

    ' Calc the pivot table
    PT.ManualUpdate = False
    PT.ManualUpdate = True

End Sub

17. Use the Number of Days setting to group by week.