Pivotal Filter Selection Macro

[bloodmilksky]

Hi,

I have just recorded the below macro to go through and select each account number on a pivot table filter. I was just wondering if anyone knew any code that would do the below for all values in the filter instead of me going through and recording it ac number by account number?

the reason for this is that each time I run this there wont always be the same number of accounts or account numbers in the pivot table filter.

any help would be greatly appreciated.

many thanks

BMS

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Business" & Chr(10) & "Partner ID"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Business" & Chr(10) & "Partner ID"). _
        CurrentPage = "120000TST"
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Business" & Chr(10) & "Partner ID"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Business" & Chr(10) & "Partner ID"). _
        CurrentPage = "120003"
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Business" & Chr(10) & "Partner ID"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Business" & Chr(10) & "Partner ID"). _
        CurrentPage = "120005"
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Business" & Chr(10) & "Partner ID"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Business" & Chr(10) & "Partner ID"). _
        CurrentPage = "120008003" End SubAutomating Pivotal Filter.xlsm

[Paul_Hossler]

Is there something that happens between each .CurrentPage = ?

It looks like you set a page value and then clear it right away

[Bob Phillips]

Dim pi As PivotItem

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Business" & Chr(10) & "Partner ID")
    
        For Each pi In .PivotItems
        
          .CurrentPage = pi.Name
        Next
    End With

[bloodmilksky]

Hi xld,

Thank you for coming back to me.

can I just ask do I add this to my current module?

many thanks

Jamie

[Bob Phillips]

It replaces your code, which is what I thought you were asking for.

[bloodmilksky]

I was but when I entered it in I get an error runtime 1004 "unable to get the pivotFields property of the PivotTable class"

[Bob Phillips]

Maybe you should post your workbook so that we can see what is going on?

[bloodmilksky]

Here Is The Work Book

[Bob Phillips]

When you are asking us how to do something, it is a good idea to give us exact details if you are not able to adapt it yourself. Your pivottable and pivot field are named differently to what you showed.

Assuming the pivot table is the only one on the sheet, this works

Dim pi As PivotItem
 
    With ActiveSheet.PivotTables(1).PivotFields("Account Number")
         
        For Each pi In .PivotItems
             
            .CurrentPage = pi.Name
        Next
    End With

[bloodmilksky]

my appologies xld I am still very new to posting and didnt mean to offend or upset you.

would it be okay to ask another question?

[Bob Phillips]

Mate, you didn't offend or upset me, I would have given this up years ago if I was that easily offended I was just pointing it out to you as you will get quicker and better answers if you more accurately describe the problem.

By all means, keep them coming!

[bloodmilksky]

Thank you

I was just wondering how I could run the mail_range macro after each selection of an account number?

many thanks

Jamie

[Bob Phillips]

You would just call it. Assuming that is it self-supporting, doesn't need anything other than the pivot setup for it, you would just add

Call Mail_Range

after the line

 .CurrentPage = pi.Name

[bloodmilksky]

awesome thank you so much ^_^

the only problem I am having and I cant seem to find an answer on the web is if you can reference the results of the account number selection in the pivot table for the range selection in the mail_range macro. would you know how to do this if not it doesnt matter.

[Bob Phillips]

The account number is the same as the filter selection , i.e. pi.Name in my code example.

[bloodmilksky]

Hi xld,

I tried to run the macro and it failed on source selection for the below. Should the reference be different? I tried setting it to a set range but that also failed as well .

Thank you ever so much for your help I really appreciate it.

    Dim Source As Range    Dim Dest As Workbook
    Dim wb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim OutApp As Object
    Dim OutMail As Object
    Set Source = Nothing
    On Error Resume Next
    Set Source = pi.Name
    On Error GoTo 0


    If Source Is Nothing Then
        MsgBox "The source is not a range or the sheet is protected, please correct and try again.", vbOKOnly
        Exit Sub
    End If

[Bob Phillips]

Your Mail_Range macro knows nothing about pi, that is a variable in a completely different procedure. You would need to pass the value as a parameter to Mail_Range.

But that will only help so far. To set Source to a range the range must be identifiable. There is no range 122373 for instance, so what exactly are you trying to address?

[bloodmilksky]

ahhhh okay. right so my idea would be then. is there a way to change the below, so that every time a account number is selected it names that range so that then Mail_Range can reference those named ranges?

Dim pi As PivotItem  
With ActiveSheet.PivotTables(1).PivotFields("Account Number") 
     
    For Each pi In .PivotItems 
         
        .CurrentPage = pi.Name 
    Next  End With 

[bloodmilksky]

sorry for all of the questions

[Bob Phillips]

That is of course doable, but seems a tad unnecessary to me. If it needs to name the range when it is selected, and then used that named range, why not cut out the middle man and just use the range without the extra step of naming it? But either way, you need to know what the range is that is associated with each value, how do you know that?