Choose account managers from pivot table

**ronlau123** · 08-06-2011, 06:10 PM

Dear all big brothers and sister,

I receive the pivot tables from data integration department every week. How can I use macro to choose specific account managers?

In the actual file, there are more than 1000 account managers in the department. my team has 200 account managers

However, my team superviosr only want to see the data of specific account managers.

Can macro help me to chose several specific account manager's name? For example, this week, I only need Adam Singer and **** Lau. Next week, if gordon lau joined our team, is there any user-friendly macro for me to choose the account manager's name.

Thank you

**gjlindn** · 08-07-2011, 12:22 AM

Hello ronlau123. Attached is your workbook with a userform added and a button to activate it. Here is the code I added. This goes in the userform:

Please Login or Register  to view this content.

And this goes in a module, so you can call the userform:

Please Login or Register  to view this content.

**ronlau123** · 08-07-2011, 12:51 PM

Thanks for your reply.

However, it shows the error message of "RUN TIME ERROR 438" Object doesn't support this property or method.

By the way, may I ask is there any macro that can chose specific account managers without one by click it.

For example, I have the sheet" account manager", it has all manager's name that I would like to select, then, in pivot table, I don't need to one by one select.

Appreciate your help. Thanks.

**watersev** · 08-09-2011, 04:36 AM

hi, ronlau, please check attachment, run code "ShowMyManagerSelection"

The pivot shows all the managers present in Account Manager list. If Account Manager sheet is not found the code quits.

Please note that all sheet names are hardcoded so in case of any changes the code must be amended as well.

**ronlau123** · 08-09-2011, 09:40 AM

Dear watersev,

Thanks for your help. May I ask what is meaning of the following sentence

Please note that all sheet names are hardcoded so in case of any changes the code must be amended as well?

Do you mean that sheet name (for example, Yasaman Deep in the account name list should be same as to the name in the pivot table.

Thanks

**watersev** · 08-09-2011, 10:32 AM

no, I mean that following sheet names:

- Account Manager
- PivotTables(1)

are used in the code. If you change them in the workbook you will need to change them in the code as well.

**ronlau123** · 08-11-2011, 12:10 AM

Dear Watersev,

Thank you very much. It is very good macro. However, as I used to another pivot table file. It doesn't work.

It is a big pivot table. The RM name column is in column E , The account manager name in the pivot table start on row 11.

I posted the new file. Would you let me know if the field name of pivot table is changed , for example RM name to Employee name, which part of code should i change . Thanks

**watersev** · 08-13-2011, 03:51 PM

hi, ronlau, the code does not work as you correctly suggested due to field name change from "RM Names" to "Employee Name"

I added remarks lines everywhere the changes have been done. Another way is to use Ctrl+F:

Find = "RM Names"
Replace = "Employee Name"

After correcting field name the code gives output as expected.

**ronlau123** · 08-13-2011, 07:54 PM

Dear watersev,

Actually, there are many sheets in the real pivot table. I don't mind that for every sheet I need to insert the module. However, may I ask why the macro can't work on the sheet "pivot table_mutual fund". The field name is also the same, employee name.

May I ask is it because of the field name of employee name is in column E. Thank You very much. You help me a lot.

Appreciate for your help.

I used the following macro:

Please Login or Register  to view this content.

**watersev** · 08-14-2011, 07:25 AM

hi, ronlau, please try the following code, it has been changed completely. The code will ask to input field number for the pivot you want to filter, this will eliminate cases of different field names.

For example: for "PivotTables(1)" sheet the index number to input is 1, for "pivot table_mutual fund" - 3. After filter is applied the filtered field is selected to visualize filtered field.

**ronlau123** · 08-14-2011, 11:07 AM

Dear Watersev,

Thank you. The macro is very useful.

Especially, for

Please Login or Register  to view this content.

I don't need to type the sheet name in the macro. Thank a lot. Appreciate your help.

Choose account managers from pivot table

LinkBack

Thread Tools

Rate This Thread

Display

Choose account managers from pivot table

Re: macro for choosing account managers from pivot table

Re: macro for choosing account managers from pivot table

Re: macro for choosing account managers from pivot table

Re: macro for choosing account managers from pivot table

Re: macro for choosing account managers from pivot table

Re: macro for choosing account managers from pivot table

Re: macro for choosing account managers from pivot table

Re: macro for choosing account managers from pivot table

Re: macro for choosing account managers from pivot table

Re: macro for choosing account managers from pivot table

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions