Hi,

I am writing a code to create a pivot table. Part of the code is:

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, _
SourceData:=DataRange, tabledestination:=Destination, tablename:="SupplierInfo"

Whenever I run the Macro and error occurs saying the Pivot Table field name is not valid and data must be organised as a list with labled columns. If Iam changing the name of the Pivottable field, I must type in a new name for the field.

What am I doing wrong?

Many thanks

Becky

Hi Becky

Without seeing how your data is set up from what you said above it seems that your DataRange is not picking up the full table including the column labels which are taken by the system and converted to the pivottable field names.

The example code below sets the DataRange as a table whose column labels start in cell A9 and the destination on another sheet.

You will notice you need to pick up in full the table that contains the data to be pivoted so that when you populate the pivot table the computer knows which fields to use.

Sub MyPivot()

Dim DataRange As Range
Dim Destination As Range
Dim PvtTable As PivotTable

Sheets(“Totals”).Select

Set DataRange = Range("A9",Range(“A9”).End(xlToRight).End(xlDown)
Set Destination = Sheets("Sales Summary").Range("A12")

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, _
SourceData:=DataRange, TableDestination:=Destination, TableName :="SupplierInfo"

Application.CommandBars("PivotTable").Visible = False 'Hides toolbar
ActiveWorkbook.ShowPivotTableFieldList = False 'Hides Field List

Set PvtTable = Sheets("Sales Summary").PivotTables("SupplierInfo")

With PvtTable

.PivotFields(“Salesperson”).Orientation = xlPageField
.PivotFields(“Year”).Orientation = xlRowField
.PivotFields(“Make”).Orientation = xlColumnField
.PivotFields(“Selling Price”).Orientation = xlDataField
.PivotFields(“Selling Price”).Function = xlSum

End With

End Sub

Hope this helps

If you still have problems send your workbook to my attention to:

info AT microsofttraining.net

Regards

Carlos