Sleeper: Copying specific data selected with Filters

[BexleyManor]

I have a table of data to which I've applied an autofilter. I focus on two of the fields to perform the filter, one for a location and another that looks >= dates.

What I would really like is when the filters have been applied the data is selected, (But only specific columns/fields) gets copied to a new worksheet, (including field headers) And the new worksheet gets named by what is specified by the first filter (Location)!!

What you think folks?? Anyone have any kind suggestions??

Thanks.

[malik641]

I have a table of data to which I've applied an autofilter. I focus on two of the fields to perform the filter, one for a location and another that looks >= dates.
Do you mean an advanced filter? I don't believe (unless I'm mistaken) that Autofilter can search through >= dates...just specific dates.

[BexleyManor]

Hi Joe,

I can't specifically enter >= in a normal autofilter but it does give you the custom option which allows me to effectively use the >=

:-)

[malik641]

What version of Excel are you using? That's pretty interesting, what custom option is this?

Anyway to help you with your coding, it can be done with a simple Worksheet_Change event....Like if you select the other filter(s) first, and do the dated filter last, just have the event check for the dated filter cell location. If that specific cell changed, perform the copy procedure. And the Location name is not a problem.

[BexleyManor]

My friend, I'm using Office 2003!!

Apply an autofilter as normal, when the column headers obtain their drop down tabs click on one and select the Custom option after All and Top 10 options and a new window will open allowing you to do simple specifications.


Anyway to help you with your coding, it can be done with a simple Worksheet_Change event....

Simple for you maybe but I wouldn't have the first clue how to !!

Could you expand with a little code?? I would be ever so grateful :-)

[Ken Puls]

Hi Bexely,

Give this a shot to get you started. If you need any help deciphering, let us know.


ActiveSheet.AutoFilterMode = False
With Rows("1:1")
.AutoFilter
.AutoFilter Field:=1, Criteria1:=">=3/3/2005", _
Operator:=xlAnd, Criteria2:="<=3/8/2005"
ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Copy
Sheets("Sheet2").Range("A1").PasteSpecial _
Paste:=xlPasteValues
End With

This will work on a table with Dates in the first column, and pulls >= Mar 3 through <= Mar 8. It pastes all visible rows of the sheet to a new sheet.

I just mocked this up quick, but normally, instead of using Rows("1:1"), I'll assign the entire range of unfiltered data to a range variable, filter it, then copy that range as myrange.offset(1,0).specialcells(xlcelltypevisible) to the new place.

HTH,

[BexleyManor]

Hi Ken, thanks for the input.

I shall have a go at implementing it and let you know how I go on.