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?

 

[nikhil0311]

Anybody have any idea? is anyone working on my post? please reply if you are not able to understand any part of the post, let me know

 

[Anthony47]

Anybody have any idea? is anyone working on my post? please reply if you are not able to understand any part of the post, let me know

I think you need only 1 button, ie the one that copy the filtered area and paste it to the other sheet; indeed the other buttons are equivalent to the Autofilter feature that you get when buy Excel.
Then associate the button to this macro:

Sub copyfilt()
DestSh = "Sheet2"                           '<<< The Destination sheet
Sheets(DestSh).Cells.ClearContents          '!! This CLEAR ALL the Destination Sheet
Range(Range("A1"), Range("A5").End(xlDown)).Resize(, 15).Copy _
    Destination:=Sheets(DestSh).Range("A1")
End Sub

The line marked <<< should be modified according your situation; and read the comment on next line!
If you intend that the data should be copied to a newly created worksheet then we shall slightly modify the code.

Bye

 

[nikhil0311]

Thanks a Ton for your Post Anthony. I need 5 buttons as these 5 buttons. 4 buttons are derived from 4 different columns mentioned by me above. and 5th button is to generate the data. i dont wanna do manual filter. i need the button to filter the data as per the selection.

 

[nikhil0311]

is there noone who can help me out ?

 

[nikhil0311]

where are the VBA experts gone?? i am not able to find my luck

 

[nikhil0311]

can someone plz take a look at this?

 

[nikhil0311]

need some help here, why are people just viewing it, not responding anything? am i asking too much?? or it is not anyone's cup of tea?

 

[Peter_SSs]

I'm not sure if you are aware, but this is a holiday season in many parts of the world! You sound very demanding for somebody who is seeking free help from volunteers.
Please refer to #12, second paragraph

 

[nikhil0311]

Apologies Sir, but i am counting on this forum and this was the 1st time it took so long for people to reply my post that's why i was a bit concerned. I know you have excellent VBA skills, can you please look into this?