Pivotal Filter Selection Macro

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

Code:

---

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 SubAttachment 19111

---

Is there something that happens between each .CurrentPage = ?

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

Code:

---

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

---

Hi xld,

Thank you for coming back to me.

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

many thanks

Jamie

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

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

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

Here Is The Work Book

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

Code:

---

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

---

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?

Mate, you didn't offend or upset me, I would have given this up years ago if I was that easily offended :rotlaugh::rotlaugh::rotlaugh::rotlaugh: 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!

Thank you :)

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

many thanks

Jamie

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

Code:

---

Call Mail_Range

---

after the line

Code:

---

.CurrentPage = pi.Name

---

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.

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

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.

Code:

---

    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

---

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?

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?

Code:

---


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



---

sorry for all of the questions

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?

Hi BMS
When you copy code, use "Paste without formatting" (Ctrl + Shift +V) to avoid the Font tags etc.

so what I want to send is the results of the ac number selection via the Mail_Range Macro. So I guess the Identifier would be the Account Number? Or the Active Range Of the Pivotal Table ?

I guess that you mean the rows that are in the source data for the selected account? If so, would it not be okay for us to ignore the pivot and work off the original data, the accounts are all blocked.

The main reason for this is the way the data is presented. Because it needs to be easily read by the recipient. So needs to have their AC, Email, and the outstanding order numbers with the time that we are looking to resolve them.

I have another spreadsheet that sorts the rows of data in to named ranges(these are the AC Number) and once it has finished sorting them it runs through each ac number range and emails it off.

but I was told that way might have too many problems.

Sorry for this XLD I really appreciate your help I just feel stuck at the moment.

Quote:

---

Originally Posted by bloodmilksky

I have another spreadsheet that sorts the rows of data in to named ranges(these are the AC Number) and once it has finished sorting them it runs through each ac number range and emails it off.

---

Not sure I am understanding, isn't this exactly what you are trying to achieve?

Yeah its exactly what I amt trying to achieve but there are some problems I have had with it that exceed my knowledge level.

Could I send it to you to see what you think?