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 Sub19111

[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 :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!

[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?

[mdmackillop]

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

[bloodmilksky]

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 ?

[Bob Phillips]

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.

[bloodmilksky]

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.

[Bob Phillips]

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?

[bloodmilksky]

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?