Pivot Tables VBA

[kw42chan]

Hi all,

I m a newbie in using VBA and now I come across creating pivot table.

Here is the code but it said there is runtime error 438:Object doesnt support this property or method.

Sub PivotTable()
Dim Pt As PivotTable
Dim strField1, strField2 As String

strField1 = Selection.Cells(1, 1).Text
strField2 = Selection.Cells(1, 2).Text

Worksheets("sheet1").Activate

'Runtime error 438:Object doesnt support this property or method.
ActiveWorkbook.PivotCaches.create(SourceType:=xlDatabase, _
SourceData:="=Sheet1!R1C1:R500C11").CreatePivotTable TableDestination:="", _
TableName:="ItemList"

'Set a Pivot Table variable to our new Pivot Table
Set Pt = ActiveSheet.PivotTables("ItemList")

'Place the Pivot Table to Start from A3 on the new sheet
ActiveSheet.PivotTableWizard TableDestination:=Cells(1, 1)

'Move the list heading to the Row Field
'Pt.AddFields RowFields:=strField1, strField2
'Pt.AddFields RowFields:
'Move the list heading to the Data Field
'Pt.PivotFields(strField).Orientation = xlDataField

With Pt
.SmallGrid = False
.AddFields RowFields:=Array("customer_code", "liner_code"), ColumnFields:=Array("condition_code")
.PivotFields("in_date").Orientation = xlDataField
'.Format xlReport6
End With

End Sub

Could anyone plot the error? Thanks a lot!!

 

[Marcelo Branco]

Hi,

Try change this
SourceData:="=Sheet1!R1C1:R500C11")

to
SourceData:="Sheet1!R1C1:R500C11")

HTH

M.

 

[kw42chan]

Hi all,

I m a newbie in using VBA and now I come across creating pivot table.

Here is the code but it said there is runtime error 438:Object doesnt support this property or method.

Sub PivotTable()
Dim Pt As PivotTable
Dim strField1, strField2 As String

strField1 = Selection.Cells(1, 1).Text
strField2 = Selection.Cells(1, 2).Text

Worksheets("sheet1").Activate

'Runtime error 438:Object doesnt support this property or method.
ActiveWorkbook.PivotCaches.create(SourceType:=xlDatabase, _
SourceData:="=Sheet1!R1C1:R500C11").CreatePivotTable TableDestination:="", _
TableName:="ItemList"

'Set a Pivot Table variable to our new Pivot Table
Set Pt = ActiveSheet.PivotTables("ItemList")

'Place the Pivot Table to Start from A3 on the new sheet
ActiveSheet.PivotTableWizard TableDestination:=Cells(1, 1)

'Move the list heading to the Row Field
'Pt.AddFields RowFields:=strField1, strField2
'Pt.AddFields RowFields:
'Move the list heading to the Data Field
'Pt.PivotFields(strField).Orientation = xlDataField

With Pt
.SmallGrid = False
.AddFields RowFields:=Array("customer_code", "liner_code"), ColumnFields:=Array("condition_code")
.PivotFields("in_date").Orientation = xlDataField
'.Format xlReport6
End With

End Sub

Could anyone plot the error? Thanks a lot!!

Thanks a lot, i have changed a bit now prompts another...seems related to error created by the obj.

Worksheets("Sheet1").Activate

'Runtime error '424':Object required.

Thisworksheets.PivotCaches.create(SourceType:=xlDatabase, _
SourceData:="R1C1:R500C11").CreatePivotTable TableDestination:="", _
TableName:="ItemList"

 

[Marcelo Branco]

SourceData:="Sheet1!R1C1:R500C11"

If the name of the sheet that contains your data is Sheet1

M.

 

[kw42chan]

SourceData:="Sheet1!R1C1:R500C11"

If the name of the sheet that contains your data is Sheet1

M.

Hi M,

I have amended the code but it still doesnt work.

Worksheets("Sheet1").Activate

'Runtime error '424':Object required.
Thisworksheets.PivotCaches.create(SourceType:=xlDatabase, _
SourceData:="Sheet1!R1C1:R500C11").CreatePivotTable TableDestination:="", _
TableName:="ItemList"

 

[Marcelo Branco]

Hi M,

I have amended the code but it still doesnt work.

Worksheets("Sheet1").Activate

'Runtime error '424':Object required.
Thisworksheets.PivotCaches.create(SourceType:=xlDatabase, _
SourceData:="Sheet1!R1C1:R500C11").CreatePivotTable TableDestination:="", _
TableName:="ItemList"

It worked for me, but without this

Worksheets("Sheet1").Activate

And like your #1 erasing the =

ActiveWorkbook.PivotCaches.create(SourceType:=xlDatabase, _
SourceData:="Sheet1!R1C1:R500C11").CreatePivotTable TableDestination:="", _
TableName:="ItemList"


M.

 

[kw42chan]

It worked for me, but without this

Worksheets("Sheet1").Activate

And like your #1 erasing the =

ActiveWorkbook.PivotCaches.create(SourceType:=xlDatabase, _
SourceData:="Sheet1!R1C1:R500C11").CreatePivotTable TableDestination:="", _
TableName:="ItemList"


M.

My excel ver is 2000, seems doesnt work. However, thanks for your information.