Solved: Macro for filter activation in other sheet

[Nick12QB]

Hello,
I have barely any experience with visual basic, but I am creating a master file for a company that I recently started at that may need some coding.

What I have is a workbook with a list of building names and the data about the buildings in one sheet. On the second sheet, there are the building names, but the sheet also lists all of the addresses that are grouped under the building name.

What I would like to do is be able to click (or select and press a run button) any cell in the building name column on my first sheet, and then the script would take me to the second sheet, and that data is filtered to only include lines with the building name that matches the one I selected on the first page.

On the first sheet, the building name is column E and it is entitled "Building Address". it contains the same data as column D on sheet two, which is entitled "Franchise Description". Sheet one is called "Territory List", and sheet two is entitled "All Building Addresses"

I think, through writing this out, what I want to know is if there is a way to select text on one sheet and run a macro to custom filter for that text in a specific column of another sheet.

Any help would be greatly appreciated, and I feel really bad asking such a favor without prior contribution, but I am in a tight spot, and I don't have the time to learn this yet myself.

Thanks

[Zack Barresse]

Hi there, welcome to the board!

Why use VBA at all? Why not keep all the data on one sheet and put it in a Pivot Table? Then you can hide/unhide fields all day long. So if you wanted to show a single building, you'd expand that building to see all the info on it, etc. Sounds easier (and more natural) than coding a difficult and custom VBA routine.

If this isn't going to work (as long as you've tried it) then post a sample file for us to look at.

[Nick12QB]

I'm sorry, I forgot to include why I was sure I'd need two sheets for this data. The first sheet, specfically the building names and sales data, as you will see in the sample, is taken from a corporate sales report that is updated monthly. Because it is easier to use the corporate report rather than create our own sales accounting, we are building our list around theirs. Therefore, in order to update our list monthly, the primarily list (1st sheet) has to have the same number of rows as the corporate list so it can be quickly updated, without having to use access to link them up and risk having to sort with mismatches or type changes. This is the reason why I couldn't hide the extra rows.

Here is my sample document, I really appreciate your help.


By the way, My company still uses Excel 2000, they are a little cheap with their software and aren't planning on upgrading soon.

[mdmackillop]

Hi Nick
Welcome to VBAX
This code should run on your sample, but not the columns as described in your text.
Regards
MD


Sub BldgList()
With Sheets("Sheet2")
.Range("A:C").AutoFilter Field:=1, Criteria1:=Cells(ActiveCell.Row, 1)
.Activate
End With
End Sub

[Nick12QB]

So, just formatted it to my list, added a keystroke activator, and now its ready and working for any employee at my company! :thumb Thank you very much for your help. Marked: Solved.