Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Amersfoort, Utrecht, Netherlands
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    automate selection in pivottable (97)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: automate selection in pivottable (97)

    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.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Amersfoort, Utrecht, Netherlands
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automate selection in pivottable (97)

    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?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: automate selection in pivottable (97)

    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.

  5. #5
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Amersfoort, Utrecht, Netherlands
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automate selection in pivottable (97)

    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?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: automate selection in pivottable (97)

    It's <code>pi.Name</code>, not <code>piName</code>

  7. #7
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Amersfoort, Utrecht, Netherlands
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automate selection in pivottable (97)

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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: automate selection in pivottable (97)

    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.

  9. #9
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Amersfoort, Utrecht, Netherlands
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automate selection in pivottable (97)

    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).CurrentPa ge = 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)

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: automate selection in pivottable (97)

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

  11. #11
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Amersfoort, Utrecht, Netherlands
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automate selection in pivottable (97)

    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

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: automate selection in pivottable (97)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •