Dynamic pivottables

[KtM]

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

 

[Guest]

what is the error message?

 

[KtM]

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".

 

[Guest]

check the name of the Pivottable !

apuFirma & "Pivot"

this cannot be found in the pivottables on that sheet

HTH

Philip

 

[KtM]

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.

 

[Guest]

try some code like this:

dim xPTable as pivottable

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

HTH

Philip

 

[KtM]

It has the desired pivottable name, but it seems to still hangup on the
same line.
On the Excel side it displays the casual Pivottable drop-down menu,
which it should automatically create. Strange.
(used on error goto namecheck to test it from the error part, so it
SHOULD have all names there.)

Strange. Thank you for trying to help!

 

[KtM]

Just to post the solution, if someone happens to end up in the same
mess:
Do not change the pivotcache.
It's a miracle what playing with macros enough can do on debugging.

 

[KtM]

Do While qCount < 5

Set NewSheet = Sheets.Add(Type:=xlWorksheet)


fir = firm & qCount & loppu

With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;file:///H:" & fir, Destination:=Range("A1"))
.Name = "Qval"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "4,5"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With



ActiveSheet.Name = apuFirma(laskuri) & "Q" & qCount



qCount = qCount + 1




Loop

Here is the working code also. Pivottables not pivotcaches, my bad on
former message.