Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Pivot Table Error
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 then place 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 ActiveWorkbook.PivotCaches.Create(SourceType:=xl Database, SourceData:= _ "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").AddDataFiel d 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Pivot Table Error
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table error | Excel Programming | |||
Pivot Table Error | Charts and Charting in Excel | |||
Pivot Table #DIV/0! Error | Excel Discussion (Misc queries) | |||
Pivot Table error (424) | Excel Programming | |||
Pivot Table Error | Excel Discussion (Misc queries) |