Problems with autofiltering and dates I think

[bpotter]

I keep getting an error "the extract range has missing or illegal
field name"
I am filtering out the dates. I also have route numbers in column b
and when I change the code to filter this column it works fine. So I
think it is trying to look at the formula in this column and not the
values.
I think I have tried everything and at my wits end. Please help before
I throw this computer out the window.
I have marked the lines where I am getting the error message w/ *.

Sub Autofilter()

Dim CalcMode As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim cell As Range
Dim Lrow As Long
Dim FieldNum As Integer

Set ws1 = Sheets("Plunger")


Set rng = ws1.Range("A16:T" & Rows.Count)


FieldNum = 16

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With


Set ws2 = Worksheets.Add

* With ws2
* rng.Columns(FieldNum).AdvancedFilter _
* Action:=xlFilterCopy, _
* CopyToRange:=.Range("A1"), Unique:=True


Lrow = .Cells(Rows.Count, "A").End(xlUp).Row
For Each cell In .Range("A2:A" & Lrow)

Set WSNew = Sheets.Add
On Error Resume Next
WSNew.Name = Format(cell.Value, "mm-yy")
If Err.Number > 0 Then
MsgBox "Change the name of : " & WSNew.Name & "
manually"
Err.Clear
End If
On Error GoTo 0

ws1.AutoFilterMode = False

rng.Autofilter Field:=FieldNum, Criteria1:="=" &
cell.Value

ws1.Autofilter.Range.Copy
With WSNew.Range("A1")

.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With

ws1.AutoFilterMode = False

Next cell

On Error Resume Next
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
On Error GoTo 0

End With

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

 

[Ron de Bruin]

Hi bpotter

No problem with a small test with normal dates

Show us your date format or formula

 

[Dave Peterson]

Ron de Bruin sent me this message in a private email a few months ago:

See also Stephen his Autofilter notes in this PDF
http://www.oaltd.co.uk/ExcelProgRef/Ch22/ProgRefCh22.htm

The AutoFilter method of a Range object is a very curious beast. We are forced
to pass it strings for its filter criteria and hence must be aware of its string
handling behaviour. The criteria string consists of an operator (=, >, <, >=
etc.) followed by a value. If no operator is specified, the "=" operator is
assumed. The key issue is that when using the "=" operator, AutoFilter performs
a textual match, while using any other operator results in a match by value.
This gives us problems when trying to locate exact matches for dates and
numbers. If we use "=", Excel matches on the text that is displayed in the cell,
i.e. the formatted number. As the text displayed in a cell will change with
different regional settings and Windows language version, it is impossible for
us to create a criteria string that will locate an exact match in all locales.

There is a workaround for this problem. When using any of the other filter
criteria, Excel plays by the rules and interprets the criteria string according
to US formats. Hence, a search criterion of ">=02/01/2001" will find all dates
on or after 1st Feb, 2001, in all locales. We can use this to match an exact
date by using two AutoFilter criteria. The following code will give an exact
match on 1st Feb, 2001 and will work in any locale:


Range("A1200").AutoFilter 2, ">=02/01/2001", xlAnd, "<=02/01/2001"

 

[bpotter]

My formula is =IF(L17>=1,N17+L17*30.42)
Which gives me the date format of December-07. It takes the date of a
changeout and multipliesit by the amount of months that we need to
change it out. I am trying to create an order form that tells me when
to change the equipment out. Thanks for the help.

 

[Dave Peterson]

I guess I don't understand the point.

If you want to filter by a date (not a specific month), then you could use that
tip from Stephen Bullen that Ron shared with me.

If you want to filter by a month, you could filter >= the first of the month you
want and < the first of the next month.

 

[bpotter]

Main problem is that I don't want every month. I might have 20 to
change in February and 10 for the next three months. Also in my
workbook based on the data I have one so far that I change out in
2009. I am wanting to create a new worksheet for each month that shows
up and therefore having an order sheet that I can give to my vendor. I
want this macro to be self sustaining so I don't have to change it
every month and we can use it long after I am gone.

 

[Ron de Bruin]

Hi bpotter

Play with EasyFilter and see if that is working for you
http://www.rondebruin.nl/easyfilter.htm
There are a lot of dates options in this add-in

If not give more information or send me a small workbook private