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:=xlDatabase, 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.

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:=xlDatabase, 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" <[email protected]> wrote in message
news:[email protected]...
> 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:=xlDatabase, 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.

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:=xlDatabase, 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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:=xlDatabase, 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.
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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:=xlDatabase, 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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:=xlDatabase,
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.
> >
> >
> >

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:=xlDatabase, 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_week")
.Orientation = xlRowField
.Position = 1
End With

With wsExploit.PivotTables("Pivot").PivotFields("date_week")
.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:=xlDatabase, 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, 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:=xlDatabase, 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_week")
> .Orientation = xlRowField
> .Position = 1
> End With
>
> With wsExploit.PivotTables("Pivot").PivotFields("date_week")
> .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:=xlDatabase, 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
>
>

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_week")
.Orientation = xlDataField
.Name = "QCount"
.Position = 1
End With
With wsExploit.PivotTables(mytable).PivotFields("date_week")
.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:=xlDatabase, 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_week")
>> .Orientation = xlRowField
>> .Position = 1
>> End With
>>
>> With wsExploit.PivotTables("Pivot").PivotFields("date_week")
>> .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:=xlDatabase, 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

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_week")
> .Orientation = xlDataField
> .Name = "QCount"
> .Position = 1
> End With
> With wsExploit.PivotTables(mytable).PivotFields("date_week")
> .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:=xlDatabase, 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_week")
> >> .Orientation = xlRowField
> >> .Position = 1
> >> End With
> >>
> >> With wsExploit.PivotTables("Pivot").PivotFields("date_week")
> >> .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:=xlDatabase, 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
>
>