Macro works in one workbook but not another

[jameshunt83]

I have the below macro in a workbook and it works absolutely perfectly. I have then copied the macro to another workbook and it doesn't work. Any idea why?

Sub Order()
ActiveSheet.Range("$P$3:$P$1000").AutoFilter Field:=1, Criteria1:="Order"
ActiveSheet.Range("$Q$3:$Q$1000").AutoFilter Field:=2, Criteria1:=xlFilterToday, Operator:=xlFilterDynamic
Range("H1").Value = "Order"
Columns("F:F").EntireColumn.Hidden = False
Columns("G:G").EntireColumn.Hidden = False
Columns("I:I").EntireColumn.Hidden = False
Columns("S:S").EntireColumn.Hidden = False
End Sub

 

[Trevor G]

If you have copied into another workbook are you running it whilst the first workbook is still open?

Is the range data the same in the second workbook?

 

[jameshunt83]

No the other workbook is closed. Yes the ranges are identical. It has a problem with the line below and the error message is Run-time error '1004': AutoFilter method of range class failed

ActiveSheet.Range("$Q$3:$Q$1000").AutoFilter Field:=2, Criteria1:=xlFilterToday, Operator:=xlFilterDynamic

 

[Trevor G]

Is Column Q formatted as a date field column?

 

[jameshunt83]

Yes it is.

 

[jameshunt83]

However I have just noticed that if I apply a filter to that column and then click on the little filter icon it only gives the option for text filter, not date filter. However the column is definitely formatted as date

 

[Trevor G]

James try this out, look to convert the spreadsheet to display everything in text format, ie on the keyboard use the Ctrl + Key below Escape (Known as the accent key becasue of this character `). When you look at column Q you should see every date as a number like this this.

[B]Sheet1[/B]

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 75px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>Q</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Dates</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">01/04/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">21/04/2011</TD></TR></TBODY></TABLE>

[COLOR=#000000][FONT=Arial][B]Excel tables to the web >> [/B][/FONT][/COLOR][URL="http://www.mrexcel.com/forum/<a href="]http://www.excel-jeanie-html.de/index.php?f=1[/URL]" target="_blank"> Excel Jeanie HTML 4

Viewed as dates

[B]Sheet1[/B]

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 150px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>Q</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Dates</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">40634</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">40654</TD></TR></TBODY></TABLE>

[COLOR=#000000][FONT=Arial][B]Excel tables to the web >> [/B][/FONT][/COLOR][URL="http://www.mrexcel.com/forum/<a href="]http://www.excel-jeanie-html.de/index.php?f=1[/URL]" target="_blank"> Excel Jeanie HTML 4

If you don't see the dates converted as numbers then you have found your issue.

 

[jameshunt83]

In those cells is a formula that gets the date from another sheet. However the date in the other sheet is formatted as date and it is exactly the same in the other workbook where it does work

 

[jameshunt83]

If i swap around the two filter lines so it runs the date filter first, it runs that part and then stops on the second filter. So it seems to have an issue with whatever filter runs second