You should qualify the references, instead of using "ActiveSheet". The
questions sheet was active, and it didn't have a pivot table.

Also, instead of adding a formula to calculate the total, you can
include that in the pivot table.

'==================================
Sub dataexploitboard()
Dim Myfile As String
Dim mytable As String
Dim destinationtable
Dim wsExploit As Worksheet
Dim wsQuestions As Worksheet
Myfile = "tryagain.xls"
mytable = "Pivot"
Set wsExploit = Worksheets("Exploit")
Set wsQuestions = Worksheets("Questions")

destinationtable = "'[" & Myfile & "]Exploit'!R9C1"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Questions!A:A").CreatePivotTable TableDestination:= _
destinationtable, _
TableName:=mytable, DefaultVersion:=xlPivotTableVersion10

wsExploit.PivotTables(mytable).AddDataField wsExploit.PivotTables( _
mytable).PivotFields("date_week"), "Count of date_week", xlCount

With wsExploit.PivotTables(mytable).PivotFields("date_w eek")
.Orientation = xlRowField
.Position = 1
End With

With wsExploit.PivotTables("Pivot").PivotFields("date_w eek")
.Orientation = xlDataField
.Calculation = xlRunningTotal
.BaseField = "date_week"
.Name = "Total"
End With
With wsExploit.PivotTables("Pivot").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With

ActiveWorkbook.ShowPivotTableFieldList = False

End Sub

'======================================

Willow wrote:
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.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html