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").AdvancedFi lter
Action:=xlFilterCopy, _
CriteriaRange:=Range("Filter"), CopyToRange:=Range("J1:Q1"),
Unique:=False
End With
End Sub

Substitute your sheet name for Sheet2
--

Regards
Roger Govier

"Matthew" wrote in message
...
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