Prompt User to enter value and use value to filter on separate sheet

Hello Excel Forum,

First off, I'm new here and have scoured the internet for the last couple days but can't seem to find the answer for my question - please bare with me.
I have a two sheet workbook and i need a macro that does one of the following (I'm not sure which is easier to write, so I'm indifferent).

(1) On sheet 1, prompt the user to enter a value that will be used to filter the data on sheet 2
(2) On sheet 1, have a dropdown menu of possible values and use that value to filter data on sheet 2.


I hope this post meets the forums rules, I couldn't find a FAQ on the Excel Programming/VBA/Macros page.


Cheers

Hi Newguy & Welcome to the Forum,

When you say prompt the user, how will sheet 2 no what column to filter on?

Can you mock up an example worksheet with a sample of what you have and what you desire?

Either option is possible I suppose, just need to understand more on how the user is supposed to tell sheet 2 the column to act on.

Hi Jeff,

the user input will filter Column A on sheet 2.

I can't attach the actual file. However, i have a very basic mock up that i don't know how to attach or display on here...

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Here you go Jeff, i appreciate the quick reply.

Now that i mocked this up very simply, it seems so much more simple but still over my head.

Before I cobbled something together, got to ask!

If the user is going to select from the dropdown box for Region, why can't they just filter straight to the data sheet?

This will be part of a larger script, but this is only a portion of the problem thus far!

Please do not keep using the full quotes. They are not necessary and they just cause clutter.

Give this a try

Did you look at B2? There already is a dropdown with the 1-7 choices.

Hi Jeff,

I apologize for the late response but i was waiting to run the sub at work before i got back to you.

The sub worked fine at home, but One problem im experiencing now that im at work is that the sub isnt appearing when i go to 'assign' the macro to a button. More over, the sub wont run (or even appear) inside the VBA editor.

When i comment out the "option explicit " and the "(ByVal Target As Range") it appears on the list (albeit not working - obviously).

Is there some type of library necessary to run this script?

I appreciate your efforts.

This macro is a worksheet level macro and therefore does not show up in the macro name dialogue box (Alt + F8).

This macro will fire once you select from the data validation in B2.

If you want to run this macro thru the press of a button...

Copy the macro below...

Please Login or Register  to view this content.

Paste code in a Normal module

• Where to paste code
• Alt + F11 >> opens the Visual Basic Editor (VBE)
• Ctrl + R >>opens the Project Explorer (if not already open on left side of screen
• Insert menu >> Module or Alt + I, M >> activates the Insert menu and inserts a Standard Module
• Paste code >> Ctrl + V (right side of screen)
• Alt + Q >> exits VBE and returns to Excel
• Back in Excel >> Alt + F8 >> Macro Dialog Box >> Highlight macro >> Run

After you've tested it this way, now place a button on the sheet via the developer tab >> Controls >> Insert >> Button

Now attach the code to this button