Using auto Filter with Macro

[mbg1261]

I'm new to working with macros, and was wondering if it's possible when using a Macro to Auto Filter to use first use a message box to pick the critieria. For Example can I have a message pop up to ask which payroll period do you want? and then the user selects say 1, 2, 3, or 4 and then have the macro sort on whatever the user has entered? Any help would be appreciated!

 

[Smitty]

Welcome to the Board!

How about something along these lines:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> CustomFilter()
    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
        x = Application.InputBox("Please enter a Pay Period Number", "Pay Period", Type:=1)
        
        Worksheets("Sheet1").Range("A1").AutoFilter Field:=1, Criteria1:=x

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Smitty

 

[Yogi Anand]

Hi mbg1261:

Welcome to MrExcel Board!

I am sorry, I don't get it ...

Let us see ... if your PayPeriods 1 through 4 are in corresponfing columns 1 through 4 with your data starting in column A.

then I think in Smitty's macro, after you get the PayPeriod prompted, then if you use Field:=x, Filtering will be done on the corresponding PayPeriod, but I still don't get what criteria is to be used in executing the Filter.

 

[Smitty]

Yogi, this is the layout I was imagining:

Book1
	A	B	C	D	E	F
1	PayPeriod	Employee	Pay	Fed	St	SDI
2	1	Bob	$2,250	$630	$135	$90
3	1	Fred	$1,750	$490	$105	$70
4	1	Paul	$3,650	$1,022	$219	$146
5	2	Bob	$2,250	$630	$135	$90
6	2	Fred	$1,750	$490	$105	$70
7	2	Paul	$3,650	$1,022	$219	$146
8	3	Bob	$2,250	$630	$135	$90
9	3	Fred	$1,750	$490	$105	$70
10	3	Paul	$3,650	$1,022	$219	$146
11	4	Bob	$2,250	$630	$135	$90
12	4	Fred	$1,750	$490	$105	$70
13	4	Paul	$3,650	$1,022	$219	$146
14	5	Bob	$2,250	$630	$135	$90
15	5	Fred	$1,750	$490	$105	$70
16	5	Paul	$3,650	$1,022	$219	$146
Sheet1

Smitty

 

[Yogi Anand]

Hi Smitty:

Thanks ... Got It! now I think I understand what OP meant by sorting.