I would prefer the last one but with the variables declared as Range coz

1. You are accessing the range object directly
2. Any changes to the code (sheetname or the range reference) at a later
stage would be easy to make ..Just one place to edit and that too at top of
the procedure..

Dim SourceRng As Range
Dim CritRng As Range
Dim CopyToRng As Range

--
Jacob


"RussellT" wrote:

Thanks in advance.
Which of the following ways is the best coding convention and why?
Private Sub()
Sheets("DevData").Range("SourceDev").AdvancedFilte r Action:=xlFilterCopy,
CriteriaRange:=Sheets("FilterControl").Range("A28: Y29"), _
CopyToRange:=Sheets("Results").Range("A2:W2"), Unique:=False
end sub
Public Sub FilterDbase_Click()
Dim filtercontrolSheet As Worksheet
Dim resultsSheet As Worksheet
Dim devdataSheet As Worksheet
Set filtercontrolSheet = Sheets("FilterControl")
Set resultsSheet = Sheets("Results")
Set devdataSheet = Sheets("DevData")

devdataSheet.Range("SourceDev").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=filtercontrolSheet.Range("A28:Y29") , _
CopyToRange:=resultsSheet.Range("A2:W2"), Unique:=False
End Sub

Public Sub FilterDbase_Click()
Dim SourceRng
Dim CritRng
Dim CopyToRng
Set SourceRng = Sheets("DevData").Range("SourceDev")
Set CritRng = Sheets("FilterControl").Range("A28:Y29")
Set CopyToRng = Sheets("Results").Range("A2:W2")

SourceRng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=CritRng, _
CopyToRange:=CopyToRng, Unique:=False
End Sub