Pivot table creation error VBA XLS
 

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.

Pivot table creation error VBA XLS
 

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.

Pivot table creation error VBA XLS
 

Actually, you're right in some how, the macro recording function was wrong in
some terms, that I fixed (well I thought !) and the tricky point (error)
remains on the With-statement. I also tried your synthax but it didn't
resolve the error, for my deeply misery !!
Many thanks anyway Farrell77 for this awaited help...
I hope somebody encountered this before as well...
Willow.

"farrell77" wrote:

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.

Pivot table creation error VBA XLS
 

Okay, maybe somebody more experienced can see what's wrong.
I'll be interested to understand this better.

I suspect the problem is with the CreatePivotTable method in the
statement just above your With statement and that the error on the
With statement occurs because myTable didn't get created.

Good luck!

- Bob


"Willow" wrote in message
...
Actually, you're right in some how, the macro recording function was wrong
in
some terms, that I fixed (well I thought !) and the tricky point (error)
remains on the With-statement. I also tried your synthax but it didn't
resolve the error, for my deeply misery !!
Many thanks anyway Farrell77 for this awaited help...
I hope somebody encountered this before as well...
Willow.

"farrell77" wrote:

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.

Pivot table creation error VBA XLS
 

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

Pivot table creation error VBA XLS
 

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

Pivot table creation error VBA XLS
 

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

Pivot table creation error VBA XLS
 

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