Finding values

[saras]

Hello,

I enclosed a sample work book for your quick reference.

There are different buttons arranged here to achive different result.
forall buttons, data comes from different files. All these files data will be incorporated in this file in the form of pivot tables only. I was unable to build the code after a limit.

My requirement is as below
1. Hit on "Name" button and search box appears.
2. Enter the search field in the search box.
3. Customer has a choice to hit on any buttons "A", "C", "P", "B", "SP". Where in it should give information of only customer which entered in "Name" box.
Ex: if i enter A in Name box, and then it on Button "C", it should go to all hidden sheet C, extract information or C

Please do let me know if you require more information from me on this.

Thanks
Saras

 

[Luke M]

Oops, looks like the workbook didn't get attached. Can you try again?

 

[saras]

Hello Luke,

Sorry, i missed it. enclosed it now.

 

[PeterB]

Hi Saras,

Welcome to the Chandoo forums.

Please can you talk me through the logic of point 3.

1. What is the relevance of buttons A,C,P,B and SP?
   • What do you want to happen when a user selects button A that is different to when a user selects button C?
   • Does clicking on a specific button determine which worksheet we go to to extract the data?
2. How do we use the value entered in the name box?
   • Does this filter the pivot table?
   • If so, on which field in the pivot table?
3. What information do you want to extract from the pivot table?

With all of the above questions, please be as specific as possible in your answers as this will help us guide you in the right direction.

Regards,

Peter

 

[saras]

Hello Peter,

Thank you for reply.

A, C, P, B, SP are the different action buttons. So when the user clicks on Name and enters a value like "A",
they can get the different types of information for "A" using all information buttons (A, C, P, B, SP).
Ex: Button A gives sales information for customer A.
Button C gives purchase information for customer A and etc....

So, information for all buttons A, C, P, B & SP will be stored n dfferent hidden sheets in the form of pivot tables. And pivot tables are fed details using external links to base files.

For customer A, when i hit on button A, i want this to go to "RM" (hidden sheet) and auto filter in two pivot tables that are in RM sheet, copy only table, hide sheet and paste it in "Sample" sheet as two different tables.

same way, Button C should go to "C" sheet (Hidden) and get information for only customer A, paste it in "Sample" sheet and hide sheet C.

P button should unhide sheet "ACP", extract information for customer A only and paste it as a table in "Sample" sheet. hide "ACP" sheet.

To simplify, users are trying to see information for any customer in various fields likes Sales, purchases, stock, transport etc... which are nothing but different action buttons given as A, C, P, B & SP.

please do let me know if you need more information from me.

thanks
saras

 

[PeterB]

Hi Saras,

For customer A, when i hit on button A, i want this to go to "RM" (hidden sheet) and auto filter in two pivot tables that are in RM sheet

Filter on which field? DU? Is that the customer field?
On worksheet C, there isn't a DU field, is the customer field D on this worksheet?
How is the VBA supposed to know which field is to be filtered for any given pivot table?

copy only table, hide sheet and paste it in "Sample" sheet as two different tables

Do you want the entire filtered pivot table copied to the sample worksheet?
If so, which pivot tables go to which cells?
Bear in mind that pivot tables are dynamic (they alter in size depending on how they are filtered) so you risk one pivottable overlapping another.

P button should unhide sheet "ACP", extract information for customer A only and paste it as a table in "Sample" sheet. hide "ACP" sheet.

There is no data in sheet ACP so I am presuming you just want to see how the code would work with Sheets RM and C.

To simplify, users are trying to see information for any customer in various fields likes Sales, purchases, stock, transport etc... which are nothing but different action buttons given as A, C, P, B & SP

It would help tremendously if you didn't obfuscate your workbook with letter codes. If it relates to Sales, please call the button Sales.

Regards,

Peter

 

[saras]

Filter on which field? DU? Is that the customer field? --- Yes, please. for RM sheet cells B1 & L1 are DU fields
On worksheet C, there isn't a DU field, is the customer field D on this worksheet? -- Yes, cell B1 should be the value t use for filter.
How is the VBA supposed to know which field is to be filtered for any given pivot table? --- Name entered in "Name" button that is search button is the field to beused as identification in all pivot tables. Because al pivot tables will have the common value for DU field (sorry, i missed it on worksheet C)
Do you want the entire filtered pivot table copied to the sample worksheet? - instead of copying entire pivot tables, please copy them as simple tables
If so, which pivot tables go to which cells? (RM Sheet: A4:F11 & K4:N11).(C sheet A5:H14)
Bear in mind that pivot tables are dynamic (they alter in size depending on how they are filtered) so you risk one pivottable overlapping another. --- after each action button, before pasting new data, previous data should be cleared

Sure please, i will have buttons re-named.

All my responses are underlined please.

 

[PeterB]

Hi Saras,

Thanks for the updated information. Unless someone else responds first, I will look at it later today and get back to you.

Regards,

Peter

 

[PeterB]

Hi Saras,

Please find attached a workbook which should do as you require.

I have modified it slightly so that the customer code (A,B,C etc) is shown on the face of the Sample worksheet so that you can see which customer you are looking at the report for.

To use it:

• Click on name (or type in cell C3)
• Click on Sales
• Click on Purchases

Kind regards,

Peter