Filter data and copy to new worksheet macro code

[nikhil0311]

I have an excel sheet with below columns and data.

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Client Event Tracking
2
3										Date	Aug-15	Jul-15	Jan-15	Jul-14
4										New Event	Pebble Beach	US Open	Leadership	US Open
5	Client Name	CASID	Banker	Banker Email	Group	Executive Title	Executive Actual Title	Executive Name
6	Abc Inc	123	Dan, H		Energy	CEO					Accepted		Yes
7	Xyz LLC	231	Sam, K		Northeast	CFO						No		Postpone
8	PQR Ltd	876	Nik, K		Midwest	Treasurer					Yes
9	BBQ Equity	435	Sank, M		Energy	Other							Maybe
10	Dominos Inc	232	Dee, G		Technology	CEO						Check		Change
Output

Now from the above data, i want to see data for Client 'Abc Inc' ONLY. So, I will put filter on Column A5 and select the client from Column A5 and then paste the visible data to new worksheet. see below for the output.

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1	Client Event Tracking
2
3										Date	Aug-15
4	3		2		1					New Event	Pebble Beach
5	Client Name	CASID	Banker	Banker Email	Group	Executive Title	Executive Actual Title	Executive Name
6	Abc Inc	123	Dan, H		Energy	CEO					Accepted
Sheet1

similarly if i want to see data for Group - Energy, then i will go to column E5, select energy and copy paste the visible data to new worksheet

basically i want to create 5 button so the user can select the criteria based on below

Button1 - Client - when click on this button, one should be able to see all the client list and then select any of the client/clients. similarly for Banker, Group, Event button mentioned below
Button2 - Banker
Button3 - Group
Button4 - Event
and then when one click on 5th button i.e. Generate one should get the output based on the above filters in the new worksheet.

Let me know if you have any doubts?

 

[Anthony47]

In addition to what Peter sayd, I am still convinced that you are solicitating people in developing a useless feature, ie replacing the Excel filter button with another button; Excel experts indeed have the attitude to exploit at their maximum the features the program offer (and you payd to get them).
That said, I spent some time developing an interface a little bit more sophisticated that the filter buttons.
This is demonstrated in the sample workbook that you can download from here: https://www.dropbox.com/s/75utb1r93mb5wz8/FormFilter.xls?dl=0

Starting from Sheet1, use the available button to start a form.
It will show 4 listboxes that will be filled by the available filter values; the worksheet get filered as you select an available value.
Indeed the form is not fully programmed:
-listbox1, 2 and 3 are populated with the available list of value
-listbox1 and 2 also apply the filter when a value is selected
So Listbox4 need to be populated with the list of values, and Listbox3 and 4 need the filter mechanism to be added.
You have to replicate what has been done with Listbox1 and 2 (and partially Listbox3):
-populate the Listbox on ListBox_Enter event macro
-manage the filter on ListBox_Change event

There are two commandbutton with a basic programming: one just invoke the Sub CopyFilt that I already suggested (it will copy the filtered worksheet to anothe sheet), the other remove all filters and close the userform.

If you feel the same approach can be useful in your workbook, then you can "Export" the form from my workbook and import it in your wb: select the form in the Project-VbaProject (left frame of vba editor), Menu /File /File Export
This will create a ".frm and a ".frx" file; then from vba of your workbook excecute a File /Import of the ".frm" file.

Bye

 

[nikhil0311]

HI Anthony. This is what i am looking for. Thnx for the User form. this is really helpful. I was not aware about the auto filter criteria of Excel. Excel is very Vast and people like you gives an opportunity to people like me to learn excel stuff. Thnx a Lot and Happy New Year in Advance.