Windows XP Pivot table field names invalid


Joined
Sep 12, 2008
Messages
1
Reaction score
0
InvoiceInv DateCompanyNameRefEvent CodeEvent NameEvent DateQty NetVatGrossPaidPay DatePay MethodCardCheque No.

I have an existing worksheet I wish to pivot table to a seperate sheet.

I have these headings in the existing sheet.

I am confused as to what the pivot table fields names should be as I keep getting the macro to stop with pivot table field name is not valid.

Here is the code, can you help please I am not an excel or macro expert so please be aware I may have created some very juvenile errors?

Sub CreatePivotFinal()
'
' CreatePivotFinal Macro
' Macro recorded 7/14/2004 by Erikka
'
Sheets("Summary_by_Payment_Method").Select
Rows("1:1000").Select
Selection.Insert Shift:=xlDown
ActiveCell.FormulaR1C1 = "'Pay Method'"
Range("A16").Select
ActiveCell.FormulaR1C1 = "'Credit Card Type'"
Range("A17").Select
ActiveCell.FormulaR1C1 = "'Event Code'"
Range("A7").Select
ActiveCell.FormulaR1C1 = "'Gross Amount'"
Range("A13").Select
'.Range ("A1"),
'create the pivot table in the sheet named Summary_by_Payment_Method
Sheets("Summary_by_Payment_Method").Select
Cells.Select
Selection.Clear
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:="'Accounts Extract'!R3C1:R1000C18").CreatePivotTable _
TableDestination:="Summary_by_Payment_Method!R3C1", _
TableName:="SummPayM", DefaultVersion:=xlPivotTableVersion10
'ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(4, 1)
'ActiveSheet.Cells(4, 1).Select
'ActiveSheet.PivotTables("SummPayM").ColumnGrand = True
ActiveSheet.PivotTables("SummPayM").AddFields RowFields:="'Pay Method'"
'ActiveSheet.PivotTables("SummPayM").AddFields RowFields:="'Credit Card Type'"
'ActiveSheet.PivotTables("SummPayM").AddFields RowFields:="'Event Code'"
ActiveSheet.PivotTables("SummPayM").AddFields DataFields:="'Gross'"
'ActiveSheet.PivotTables("PivotTable2").PivotFields("Open SPRs").Orientation = _
xlDataField
'ActiveSheet.PivotTables("SummPayM").PivotFields("'Gross Amount'").Function _
= xlSum 'Range("D5").Select
End Sub
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top