Advanced Filter Error 1004

[Matthew]

Hi All,

I'm sure there must be an easy answer to this!?

Using Excel 2007

I have reorded a simple macro to update an advanced filter when a command
button is clicked. While recording the macro the advanced filter works and
updates correctly. When I assign the macro to my button I get:

Run-time error '1004'
Method 'Range' of object '_Whorksheet' failed

My code is:

Private Sub CommandButton1_Click()

Sheets("Assignments").Select
Range("Database").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range _
("Filter"), CopyToRange:=Range("J1:Q1"), Unique:=False

End Sub

The range 'Database' is on my 'assignments' sheet and refers to columns A:H
The range 'Filter' is on a different sheet and contains my critera

I'm sure I have used this sort of thing before in Excel 2003 without prblem...

TIA for your help

Matthew

 

[Roger Govier]

Hi Matthew

You need to specify the sheet (destination sheet).
There is no need to Select the source range, as long as you specify it fully
with sheet name as well

Private Sub CommandButton1_Click()

With Sheets("Sheet2")

Sheets("Assignments").Range("Database").AdvancedFilter
Action:=xlFilterCopy, _
CriteriaRange:=Range("Filter"), CopyToRange:=Range("J1:Q1"),
Unique:=False
End With
End Sub

Substitute your sheet name for Sheet2

 

[Dave Peterson]

I think Roger has the answer, but I'd qualify all the ranges:

Private Sub CommandButton1_Click()

Sheets("Assignments").Range("Database").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=worksheets("someothersheet").Range("Filter"), _
CopyToRange:=me.Range("J1:Q1"), Unique:=False

End Sub

Database is on Assignments
Filter is on someothersheet (you didn't say)
and
j1:q1 is on the sheet that holds the commandbutton (that's what Me means).

=====
When you recorded the macro, it was in a general module. Those unqualified
range objects refered to the sheet that was active. When you moved the code
behind the worksheet with the commandbutton, those unqualified ranges belong to
the sheet that owns the code--not the activesheet!