Hello,

I was able to get the following (slightly modified) version of your code to work. If the code below doesn't work for you, you may want to see the article at http://support.microsoft.com/kb/818808. Hope this helps.

Sub Format_Checking()
Dim wsData As Worksheet
Dim wsPivot As Worksheet
Dim pTable As PivotTable
Dim sRefersTo As String
Dim lRow As Long

Set wsData = ActiveSheet 'Sheets("Transactions")

With wsData
lRow = .UsedRange.Rows.Count 'Last row on Transactions sheet
.Columns("A:I").EntireColumn.AutoFit
sRefersTo = .Name & "!R1C1:R" & lRow & "C9" 'Pivotcache range
End With

ActiveWorkbook.Names.Add Name:="Transactions", _
RefersToR1C1:="='" & Replace(sRefersTo, "!", "'!")

Sheets.Add
Set wsPivot = ActiveSheet

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, _
SourceData:=sRefersTo, _
Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:=wsPivot.Name & "!R3C1", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12

Set pTable = wsPivot.PivotTables("PivotTable1")

With pTable.PivotFields("Date")
.Orientation = xlRowField
.Position = 1
End With

wsPivot.Range("A4").Group Start:=True, End:=True, Periods:=Array(False, False, False, _
False, True, False, False)

pTable.AddDataField pTable.PivotFields("Amount"), "Sum of Amount", xlSum

With pTable
.PivotFields("Category").Orientation = xlRowField
.PivotFields("Category").Position = 2

.PivotFields("Description").Orientation = xlRowField
.PivotFields("Description").Position = 3

.PivotFields("Transaction Type").Orientation = xlPageField
.PivotFields("Transaction Type").Position = 1

.PivotFields("Account Name").Orientation = xlPageField
.PivotFields("Account Name").Position = 2

.PivotFields("Original Description").Orientation = xlPageField
.PivotFields("Original Description").Position = 1

.PivotFields("Date").ShowDetail = False
End With

ActiveWorkbook.ShowPivotTableFieldList = False

Set wsData = Nothing
Set wsPivot = Nothing
Set pTable = Nothing

End Sub