automate selection in pivottable (97)

Topic

New Reply

Hello all,

I have the following situation:
on sheet ‘A’ there is a pivottable with a selectable filed in page level. I select the first item on this pagefield, the pivottable is updated. Now I save this worksheet to a new workbook and close it. Then I select the second item on the pagefield and so on….

I’m looking for a way to automate this by VBA, but how do I know all the items from the pagefield?
If I could store them in an array, then I can automate is al.

I hope you can help me.

Reply | Quote

Replies

You could use the ShowPages method to create a worksheet for each item.

ActiveSheet.PivotTables(1).ShowPages PageField:=”Region”

(Substitute the name of your page field). You can then move the worksheets to new workbooks.

Reply | Quote

Hello Hans,

Thanks for the reply.
Unfortunaly this will not help, because I have already more sheets in the workbook, and every sheet has a pivottable that I first have to update an then I have to save the workbook.
Any other ideas?

Reply | Quote

You could use code like this to loop through the items in the page field:

Sub LoopItems()
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Set ws = ActiveSheet
Set pt = ws.PivotTables(1)
Set pf = pt.PageFields(1)
For Each pi In pf.PivotItems
pf.CurrentPage = pi.Name
‘ Insert code to save worksheet here
…
Next pi

Set pi = Nothing
Set pf = Nothing
Set pt = Nothing
Set ws = Nothing
End Sub

Modify as needed.

Reply | Quote

Hello Hans,

Thanks for the code, but it is giving an error on the line pf.CurrentPage = piName
Fout ‘1004’ tijdens uitvoering : Eigenschap _Default van klasse Pivotitem kan niet worden ingesteld.

what is going wrong?

Reply | Quote

It’s pi.Name, not piName

Reply | Quote

Yes, you are correct… typo in this form… it is pi.name in my code

Reply | Quote

The code worked correctly in an example workbook with a pivot table, so we’d have to see your workbook to see what’s wrong.

Reply | Quote

Hello Hans,

Here is the code:

Workbooks.Open FileName:=strWrkbookPath & “” & strTmpFile, UpdateLinks:=True
strWrkbookFile = strTmpFile
Set objWS = ActiveSheet
Set objPT = objWS.PivotTables(1)
Set objPF = objPT.PageFields(1)

For Each objPI In objPF.PivotItems
With ActiveWorkbook
For intCount1 = 1 To .Worksheets.Count
.Worksheets(intCount1).Select
ActiveSheet.PivotTables(1).PageFields(1).CurrentPage = objPI.Name
Next intCount1
End With
strFileName = strMI & ” ” & Range(“B1″).Value & ” ” & strMnd & “.xls”
strChkFile = objFS.BuildPath(strWrkbookPath, strFileName)
‘start procedure copy all to new workbook
‘start procedure copy – paste special values
‘start procedure page setup
‘If Not Dir(strChkFile) = “” Then
‘Kill strChkFile ‘verwijder bestand als deze al bestaat
‘End If
‘ActiveWorkbook.SaveAs FileName:=strTmpFile, FileFormat:=xlNormal, Password:=””, WriteResPassword:=””, ReadOnlyRecommended:=False, CreateBackup:=True
‘close workbook
Next
ActiveWorkbook.Close SaveChanges:=False ‘sluit het werkbestand

(I will be on holliday for the next week)

Reply | Quote

Do all pivot tables have the same page field with the same items?

Reply | Quote

Hans,

Yes, all pivot tables have the same base table, so the same pagefiled names
and the error is already showing at the first pivottable update

Reply | Quote

I really think we’ll have to see (a stripped down copy of) the workbook. You can remove sensitive data or replace it with dummy data.

Reply | Quote