Jay Petrulis or Anyone: Using a Data Filter within a Macro.

Zac · May 2nd, 2002, 11:04 AM

Using the Data Filter (D/F) is great except when you use it in a Macro. Sort of...

In the Macro I recorded, I use a D/F to display a Salesman's Commission Code (026)
in Col "Q". I was hoping to use the same macro from month to month, but it appears I can't until I solve one problem.

During execution of the macro, 20 lines are
visible (rows 40:59).
I then have a GOTO line (Application.Goto Reference:="R1C2").
I then want to go down 1 line, and then Ctrl+Shift+End to hi-light all the cells to be copied to another Sheet.

My problem:
If last month this particular salesmans commission lines ran from 50:80, the code remembered that, and drops down to 50 this time around instead of 40.
How can I correct the code to have it select what I need? Basically to goto R1C2 and only come down 1 line rather than use what was instructed last month.

Make sense...

Thanks to anyone who can help,
Zac

Jay Petrulis · May 2nd, 2002, 02:27 PM

I don't know exactly what you are doing, so please post your code.

My response in the following link uses AutoFilter in a macro. The key is to set your data range correctly. The DataRange object in the code can be used, and even refined if not all colums are needed.

http://mrexcel.com/board/viewtopic.p...7015&forum=2&1

HTH,
Jay

Sam40mUK · May 2nd, 2002, 02:42 PM

Evening Zac,
I am sorry but I can not quite get what you are driving at either, however I do use Data Filter in a Macro.
I have a list of Materials with Qty/Price/Date/Project Number - I use Advanced Data Filter to extract the Materials on a specific Project Number (Criteria) .. I then use the Extracted Data to form a Pivot Table on another Sheet.
Although I am not a Programmer I find it works well. The List Range, Criteria and Extract Range are 'fixed' and I merely operate the Data - Filter - Advanced Filter command since the ranges are already 'set'.
I hope this gives you an idea as to what I am doing and some hope that it does work in a macro!
Regards
S

Zac · May 2nd, 2002, 03:10 PM

Will give you more feedback tomorrow.

Thanks.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 2nd, 2002, 11:04 AM	#1
Zac Board Regular Join Date: Feb 2002 Location: Mike Posts: 796	Using the Data Filter (D/F) is great except when you use it in a Macro. Sort of... In the Macro I recorded, I use a D/F to display a Salesman's Commission Code (026) in Col "Q". I was hoping to use the same macro from month to month, but it appears I can't until I solve one problem. During execution of the macro, 20 lines are visible (rows 40:59). I then have a GOTO line (Application.Goto Reference:="R1C2"). I then want to go down 1 line, and then Ctrl+Shift+End to hi-light all the cells to be copied to another Sheet. My problem: If last month this particular salesmans commission lines ran from 50:80, the code remembered that, and drops down to 50 this time around instead of 40. How can I correct the code to have it select what I need? Basically to goto R1C2 and only come down 1 line rather than use what was instructed last month. Make sense... Thanks to anyone who can help, Zac

May 2nd, 2002, 02:27 PM	#2
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	I don't know exactly what you are doing, so please post your code. My response in the following link uses AutoFilter in a macro. The key is to set your data range correctly. The DataRange object in the code can be used, and even refined if not all colums are needed. http://mrexcel.com/board/viewtopic.p...7015&forum=2&1 HTH, Jay

May 2nd, 2002, 02:42 PM	#3
Sam40mUK Board Regular Join Date: Mar 2002 Location: North Wales Coast, UK Posts: 95	Evening Zac, I am sorry but I can not quite get what you are driving at either, however I do use Data Filter in a Macro. I have a list of Materials with Qty/Price/Date/Project Number - I use Advanced Data Filter to extract the Materials on a specific Project Number (Criteria) .. I then use the Extracted Data to form a Pivot Table on another Sheet. Although I am not a Programmer I find it works well. The List Range, Criteria and Extract Range are 'fixed' and I merely operate the Data - Filter - Advanced Filter command since the ranges are already 'set'. I hope this gives you an idea as to what I am doing and some hope that it does work in a macro! Regards S

May 2nd, 2002, 03:10 PM	#4
Zac Board Regular Join Date: Feb 2002 Location: Mike Posts: 796	Will give you more feedback tomorrow. Thanks.