Sounds like you're a master, hopefully I don't have your email !!!!
Many many thanks Debra.




"Debra Dalgleish" wrote:

It's best if you mention the version in your initial post. The following
code should work in Excel 97:

'================================
Sub dataexploitboard97()
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"

wsQuestions.PivotTableWizard SourceType:=xlDatabase, _
SourceData:=wsQuestions.Name & "!" & wsQuestions.Cells(1, 1) _
.CurrentRegion.Address(ReferenceStyle:=xlR1C1), _
TableDestination:=destinationtable, TableName:=mytable
wsExploit.PivotTables(mytable).AddFields _
RowFields:=Array("date_week", "Data")
With wsExploit.PivotTables(mytable).PivotFields("date_w eek")
.Orientation = xlDataField
.Name = "QCount"
.Position = 1
End With
With wsExploit.PivotTables(mytable).PivotFields("date_w eek")
.Orientation = xlDataField
.Name = "QTotal"
.Calculation = xlRunningTotal
.BaseField = "date_week"
End With
With wsExploit.PivotTables(mytable).PivotFields("Data")
.Orientation = xlColumnField
.Position = 1
End With
End Sub
'===============================

Willow wrote:
Debra, that works on my Excel 2003 and this is perfect, but (it would have
been too easy !)my client is running with Excel 97 which don't support all
the methods you use... do you have any idea about change your code in order
to make it work on Excel 97 ?

Oh many thanks in advance

"Debra Dalgleish" wrote:


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





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