Run time error 1004-Auto filter

**jad70** · 01-12-2009, 08:28 AM

Hi again,

Well ,another small but annoying problem with my data.
I have 4 command buttons (A-D) so that the users can update after inputting new or revised Data.
My problem is that if updated in order, button D Macro below stops at the first "Selection.AutoFilter..." as if it cannot turn on the Auto Filter.

It will run if I manually set the AutoFilter on the sheet.

I think, and as I am completely new to this please feel free to correct me, the reason that it stops is that on the Button B is a "AdvancedFilter" Macro.
I have to present this on Wednesday the 14th so any help provided will be gratefully recieved.

Sub manhours()
'
' manhours Macro
' Macro recorded 07/01/2009 by DORIS
'

'
    Sheets("All Intervals").Select
    Application.ScreenUpdating = False
    Range("A15:A164").Select
    Selection.Copy
    Sheets("MH's").Select
    Range("A8").Select
    ActiveSheet.Paste Link:=True
    Sheets("All Tasks").Select
    Selection.AutoFilter Field:=6, Criteria1:="FH"
    Range("F2").Select
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("H1:H806").Select
    Selection.Copy
    Sheets("MH's").Select
    Range("C9").Select
    ActiveSheet.Paste Link:=True
    Sheets("All Intervals").Select
    Range("S15:S34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("MH's").Select
    Range("F8").Select
    ActiveSheet.Paste Link:=True
    Sheets("All Tasks").Select
    Selection.AutoFilter Field:=6, Criteria1:="FC"
    Range("F47").Select
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("H1:H806").Select
    Selection.Copy
    Sheets("MH's").Select
    Range("H9").Select
    ActiveSheet.Paste Link:=True
    Sheets("All Intervals").Select
    Range("AJ15:AJ94").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("MH's").Select
    Range("K8").Select
    ActiveSheet.Paste Link:=True
    Sheets("All Tasks").Select
    Selection.AutoFilter Field:=6, Criteria1:="EH"
    Range("F250").Select
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("H1:H806").Select
    Selection.Copy
    Sheets("MH's").Select
    Range("M9").Select
    ActiveSheet.Paste Link:=True
    Sheets("All Intervals").Select
    Range("BA17:BA22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("MH's").Select
    Range("P8").Select
    Sheets("All Intervals").Select
    Range("BA15:BA22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("MH's").Select
    ActiveSheet.Paste Link:=True
    Sheets("All Tasks").Select
    Selection.AutoFilter Field:=6, Criteria1:="APU Hrs"
    Application.CutCopyMode = False
    Range("F381").Select
    Selection.Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("H1:H806").Select
    Selection.Copy
    Sheets("MH's").Select
    Range("R9").Select
    ActiveSheet.Paste Link:=True
    Sheets("All Intervals").Select
    Range("BR15:BR22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("MH's").Select
    Range("U8").Select
    ActiveSheet.Paste Link:=True
    Sheets("All Tasks").Select
    Selection.AutoFilter Field:=6, Criteria1:="APU CY"
    Application.CutCopyMode = False
    Range("F225").Select
    Selection.Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("H1:H806").Select
    Selection.Copy
    Sheets("MH's").Select
    Range("W9").Select
    ActiveSheet.Paste Link:=True
    Sheets("All Intervals").Select
    Range("CI15:CI704").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("MH's").Select
    Range("Z8").Select
    ActiveSheet.Paste Link:=True
    Sheets("All Tasks").Select
    Selection.AutoFilter Field:=6, Criteria1:="Cal"
    Application.CutCopyMode = False
    Range("F4").Select
    Selection.Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("H1:H806").Select
    Selection.Copy
    Sheets("MH's").Select
    Range("AA8").Select
    ActiveSheet.Paste Link:=True
    Application.ScreenUpdating = True
    Application.Goto Sheet1.Range("A1"), True
End Sub

I have looked at the other post regarding this error but my list is correctly formatted.

Many thanks in advance.

**royUK** · 01-12-2009, 08:33 AM

Recording macros is fine, but you must remember the code generated is not very efficient and is best edited. For example you don't need to select ranges.

You can check if a sheet is autofiltered

 If Not ActiveSheet.AutoFilterMode Then   ActiveSheet.cells(1,1).AutoFilter

**jad70** · 01-12-2009, 08:41 AM

Hi Roy,

I have tidied up a lot of this (and all the other) macros from using this forum, a great source of info for a novice like myself.
Many thanks for you tip, but a couple of questions:
When you say the I do not have to select ranges, what do you mean? sorry if this sounds a bit stupid but I just want to make sure of what you mean before I alter anything, and
Where would this line of code you gave me go? Again sorry for sounding a bit stupid but still learning.

John.

**jad70** · 01-12-2009, 10:31 AM

Roy,

Answered my own question buy trial and error, many thanks for the code. The macros all run now.

Still, how do I clean up the original macro to avoid selecting ranges etc?

John.

**royUK** · 01-12-2009, 10:41 AM

This is only for guidance

With Sheets("All Tasks")
If Not .AutoFilterMode Then .Cells(1, 1).AutoFilter
 .UsedRange.AutoFilter Field:=6, Criteria1:="FH"
End If
End With

Generally it is unnecessary to select a range, sheet or workbook in VBA

**jad70** · 01-12-2009, 11:49 AM

Roy,

Many thanks. Every day is a learning day......

**jad70** · 01-12-2009, 12:11 PM

Roy,

so, in the process of 'cleaning up' this code, and now when I run it I get a Compile error- Expected End With. So I change it and the get a -Expected End Sub. I am still only about a third through getting rid of the selects and using 'with'. Will this problem dissappear when I have finished?

**nigelog** · 01-12-2009, 12:23 PM

Jad
if you get expected "end with" or expected "end sub" errors, all it means is gthat they are not paired. If you use a With you have to use an End With before moving on to the next.

**royUK** · 01-12-2009, 12:31 PM

I've edited the example

**jad70** · 01-12-2009, 12:45 PM

Nigelog,

I think I might be tying myself up in knots here. I am still getting the error and have tried to 'end With' where I see it, but I cannot see the problem.

Sub manhours()
'
' manhours Macro
' Macro recorded 07/01/2009 by DORIS

'
    Application.ScreenUpdating = False
    With Sheets("All Intervals")
    Range("A15:A164").Copy
    With Sheets("MH's")
    .UsedRange ("A8")
    ActiveSheet.Paste Link:=True
    With Sheets("All Tasks")
    If Not .AutoFilterMode Then .Cells(1, 1).AutoFilter
    .UsedRange.AutoFilter Field:=6, Criteria1:="FH"
    End With
    .UsedRange ("F2")
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    .UsedRange("H1:H806").Select
    Selection.Copy
    With Sheets("MH's")
    .UsedRange ("C9")
    ActiveSheet.Paste Link:=True
    With Sheets("All Intervals")
    .UsedRange ("S15:S34")
    Application.CutCopyMode = False
    Selection.Copy
    With Sheets("MH's")
    .UsedRange ("F8")
    ActiveSheet.Paste Link:=True
    With Sheets("All Tasks")
    If Not .AutoFilterMode Then .Cells(1, 1).AutoFilter
    .UsedRange.AutoFilter Field:=6, Criteria1:="FC"
    End With
    .UsedRange ("F47")
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    .UsedRange("H1:H806").Select
    Selection.Copy
    With Sheets("MH's")
    .UsedRange ("H9")
    ActiveSheet.Paste Link:=True
    With Sheets("All Intervals")
    .UsedRange("AJ15:AJ94").Select
    Application.CutCopyMode = False
    Selection.Copy
    With Sheets("MH's")
    .UsedRange ("K8")
    ActiveSheet.Paste Link:=True
    With Sheets("All Tasks")
    If Not .AutoFilterMode Then .Cells(1, 1).AutoFilter
    .UsedRange.AutoFilter Field:=6, Criteria1:="EH"
    End With
    .UsedRange ("F250")
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    .UsedRange ("H1:H806")
    Selection.Copy
    With Sheets("MH's")
    .UsedRange ("M9")
    ActiveSheet.Paste Link:=True
    With Sheets("All Intervals")
    .UsedRange ("BA17:BA22")
    Application.CutCopyMode = False
    Selection.Copy
    With Sheets("MH's")
    .UsedRange("P8").Select
    With Sheets("All Intervals")
    .UsedRange("BA15:BA22").Select
    Application.CutCopyMode = False
    Selection.Copy
    With Sheets("MH's")
    ActiveSheet.Paste Link:=True
    With Sheets("All Tasks")
    If Not .AutoFilterMode Then .Cells(1, 1).AutoFilter
    .UsedRange.AutoFilter Field:=6, Criteria1:="APU Hrs"
    End With
    Application.CutCopyMode = False
    .UsedRange ("F381")
    Selection.Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    .UsedRange ("H1:H806")
    Selection.Copy
    With Sheets("MH's")
    .UsedRange ("R9")
    ActiveSheet.Paste Link:=True
    With Sheets("All Intervals")
    .UsedRange ("BR15:BR22")
    Application.CutCopyMode = False
    Selection.Copy
    With Sheets("MH's")
    .UsedRange ("U8")
    ActiveSheet.Paste Link:=True
    With Sheets("All Tasks")
    If Not .AutoFilterMode Then .Cells(1, 1).AutoFilter
    .UsedRange.AutoFilter Field:=6, Criteria1:="APU CY"
    End With
    Application.CutCopyMode = False
    .UsedRange ("F225")
    Selection.Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    .UsedRange ("H1:H806")
    Selection.Copy
    With Sheets("MH's")
    .UsedRange ("W9")
    ActiveSheet.Paste Link:=True
    With Sheets("All Intervals")
    .UsedRange ("CI15:CI704")
    Application.CutCopyMode = False
    Selection.Copy
    With Sheets("MH's")
    .UsedRange ("Z8")
    ActiveSheet.Paste Link:=True
    With Sheets("All Tasks")
    If Not .AutoFilterMode Then .Cells(1, 1).AutoFilter
    .UsedRange.AutoFilter Field:=6, Criteria1:="Cal"
    End With
    Application.CutCopyMode = False
    .UsedRange ("F4")
    Selection.Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    .UsedRange ("H1:H806")
    Selection.Copy
    With Sheets("MH's")
    .UsedRange ("AA8")
    ActiveSheet.Paste Link:=True
    Application.ScreenUpdating = True
    Application.Goto Sheet1.Range("A1"), True
    End With

End Sub

Could it be that I am tired? Any help would be great.
Roy, just read you edit and put it in but now getting End If without block If.....aaaarrrgggghhh.
So, off home now so I will catch up tomorrow. Many thanks again for your help guys.

Run time error 1004-Auto filter

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Run time error 1004-Auto filter

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions