VBA Pivot Table Error

[Michael Davis]

Hello, All:

I am having trouble with some VBA code for a Pivot Table I am trying to make. Here's what I am doing: I am recording a macro that formats my checking account transactions. These transactions are downloaded from Mint.com in CSV format. After downloading, I rename the sheet to "transactions", and thenplace all data in a table range named "Transactions". I then begin recording the Macro, and below is the code. However, when the code reaches the point of the Pivot Table I continue getting a Run-time error 1004. I have marked the error location below with ">>" as the symbol. Any help is appreciated. Here is the code:

Sub Format_Checking()
'
' Format_Checking Macro
'

'
Columns("A:I").Select
Columns("A:I").EntireColumn.AutoFit
Range("A1:I254").Select
ActiveWorkbook.Names.Add Name:="Transactions", RefersToR1C1:= _
"=transactions!R1C1:R254C9"
Range("A1").Select
Sheets.Add"transactions!R1C1:R254C9", Version:=xlPivotTableVersion12).CreatePivotTable _TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _ :=xlPivotTableVersion12
Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
.Orientation = xlRowField
.Position = 1
End With
Range("A7").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
False, True, False, False)
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Amount"), "Sum of Amount", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Category")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Description")
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Transaction Type")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Account Name")
.Orientation = xlPageField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Original Description")
.Orientation = xlPageField
.Position = 1
End With
ActiveWorkbook.ShowPivotTableFieldList = False
Range("A6").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").ShowDetail = False
Range("C1").Select
End Sub

 

[Ben McClave]

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:=xlDatabase, _
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