I'm new to pivot tables and VBA and recently had trouble with the same
error. I hope someone more experienced jumps in, in case I'm wrong
about any of this.

It looks like you tried just what I did: used the macro recording function
to generate this code. That's a great way to start, but for some reason you
apparently can't use the ActiveWorkbook.PivotCaches.Add().CreatePivotTable
method to create a pivot table. You have to use the wizard.

Try replacing your code:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Questions!A:A").CreatePivotTable TableDestination:= _
destinationtable, _
TableName:=mytable, DefaultVersion:=xlPivotTableVersion10

with something like this:

Worksheets(sheet).PivotTableWizard _
SourceType:=xlDatabase, _
SourceData:="Questions!A:A", _
TableDestination:=destinationtable, _
TableName:=mytable, DefaultVersion:=xlPivotTableVersion10

It's working for me.

- Bob

P.S. I'm grateful to Tom Ogilvie who steered me in a successful direction
with this a few weeks ago.


"Willow" wrote in message
...
Hi,
I have this following error :
"Unable to get the PivotTables property of the Worksheet class" on the
With
statement when I launch this macro.

What did I forget to declare ?

"Exploit" is the sheet on which I want the pivot table like this:
Quest_EXT 3
Quest_ITV 3
Quest_LXE 1
Quest_KMQ 2

"Questions" is the sheet of data like this:

COLUMN A
date_week
Quest_EXT
Quest_EXT
Quest_ITV
Quest_EXT
Quest_ITV
Quest_LXE
Quest_ITV
Quest_KMQ
Quest_KMQ



MACRO :


Sub dataexploitboard()
Worksheets("Exploit").Activate
Myfile = "tryagain.xls"
mytable = "Pivot"

ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("Questions").Select
Columns("A:A").Select
destinationtable = "'[" & Myfile & "]Exploit'!R9C1"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Questions!A:A").CreatePivotTable TableDestination:= _
destinationtable, _
TableName:=mytable, DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables(mytable).PivotFields("date _week")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables(mytable).AddDataField
ActiveSheet.PivotTables( _
mytable).PivotFields("date_week"), "Count of date_week", xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
Range("C11").Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
Range("C12").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+RC[-1]"
Range("C12").Select
Selection.AutoFill Destination:=Range("C12:C39")

End Sub

EXTRA-BALL :
Please do you know how to get the number of items on rows of a pivot table
?
In my exemple, the result would be 4.