Hi!
Can anyone find whats wrong with the following VBA, I cant seem to find
why

"With ActiveSheet.PivotTables(apuFirma & "Pivot").PivotFields("Stage")
.Orientation = xlDataField
.Caption = "Average of Stage"
.Function = xlAverage"
stops all the time.
The whole code part(relevant) is below.

Sheets(apuFirma & "Q" & qCount).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"'" & apuFirma & "Q" & qCount & "'!A:B").CreatePivotTable
TableDestination:="", TableName:= _
"'" & apuNimi & "Pivot" & "',
DefaultVersion:=xlPivotTableVersion10"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables(apuFirma &
"Pivot").PivotFields("Stage")
.Orientation = xlDataField
.Caption = "Average of Stage"
.Function = xlAverage
End With
ActiveSheet.PivotTables(apuNimi & "Pivot").AddDataField
ActiveSheet.PivotTables( _
apuFirma & "Pivot").PivotFields("Stage"), "Count of Stage",
xlCount
Range("A3").Select
ActiveSheet.PivotTables(apuNimi & "Pivot").PivotFields("Count of
Stage").Function _
= xlAverage

Any ideas?

what is the error message?

"KtM" wrote:

Hi!
Can anyone find whats wrong with the following VBA, I cant seem to find
why

"With ActiveSheet.PivotTables(apuFirma & "Pivot").PivotFields("Stage")
.Orientation = xlDataField
.Caption = "Average of Stage"
.Function = xlAverage"
stops all the time.
The whole code part(relevant) is below.

Sheets(apuFirma & "Q" & qCount).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"'" & apuFirma & "Q" & qCount & "'!A:B").CreatePivotTable
TableDestination:="", TableName:= _
"'" & apuNimi & "Pivot" & "',
DefaultVersion:=xlPivotTableVersion10"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables(apuFirma &
"Pivot").PivotFields("Stage")
.Orientation = xlDataField
.Caption = "Average of Stage"
.Function = xlAverage
End With
ActiveSheet.PivotTables(apuNimi & "Pivot").AddDataField
ActiveSheet.PivotTables( _
apuFirma & "Pivot").PivotFields("Stage"), "Count of Stage",
xlCount
Range("A3").Select
ActiveSheet.PivotTables(apuNimi & "Pivot").PivotFields("Count of
Stage").Function _
= xlAverage

Any ideas?

Run-Time error '1004':
Unable to get the Pivottables property of the Worksheet class.

I am not so familiar with the pivottables property, but I cant figure
easier way to get subtotal averages of 2 rows, so Im shooting in the
dark with it. (Macroed it and made it "dynamic".

check the name of the Pivottable !

apuFirma & "Pivot"

this cannot be found in the pivottables on that sheet

HTH

Philip

"KtM" wrote:

Run-Time error '1004':
Unable to get the Pivottables property of the Worksheet class.

I am not so familiar with the pivottables property, but I cant figure
easier way to get subtotal averages of 2 rows, so Im shooting in the
dark with it. (Macroed it and made it "dynamic".

Thanks, had one count of apuNimi as apuFirma(apu=help in finnish, just
temporary collections to reduce certain operations).

How can you see the pivottables of the sheet? I have not really used
pivottables much, just basic reporting type drag'n'drop pivottable
usage.

I fixed the names, so they should be OK. now(from "'" & apuNimi &
"Pivot" & "' to aPufirma, so it should now be named as supposed, unless
that "'" thingie messes it up?)

I am at a loss what to check with this one.

try some code like this:

dim xPTable as pivottable

for each xPTable in activesheet.pivottables
debug.print xPTable.name
next

HTH

Philip

"KtM" wrote:

Thanks, had one count of apuNimi as apuFirma(apu=help in finnish, just
temporary collections to reduce certain operations).

How can you see the pivottables of the sheet? I have not really used
pivottables much, just basic reporting type drag'n'drop pivottable
usage.

I fixed the names, so they should be OK. now(from "'" & apuNimi &
"Pivot" & "' to aPufirma, so it should now be named as supposed, unless
that "'" thingie messes it up?)

I am at a loss what to check with this one.