Hi MMH,

Your code relies on making physical selections This is rarely necessary or
desirable.

Try instead:

'============================
Sub Tester()

Dim Rng1 As Range, rng2 As Range
Dim rng3 As Range, rng4 As Range
Const sStr As String = "Search" '<<==== Autofilter Criterion
Dim sh As Worksheet

Set sh = Sheets("Sheet1") '<<====== CHANGE

'CHANGE A1 to the first Autofilter cell
sh.Range("A1").AutoFilter Field:=6, Criteria1:=sStr

Set Rng1 = sh.AutoFilter.Range
Set rng2 = Rng1.Offset(1).Resize(Rng1.Rows.Count - 1)
Set rng3 = rng2.Columns(6)

On Error Resume Next
Set rng4 = rng3.Offset(, -1). _
SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If Not rng4 Is Nothing Then
rng4.Name = "Search"
Else
ActiveWorkbook.Names.Add Name:= _
"Search", RefersToR1C1:="=0"
End If

Sheets("YTD Totals").Range("E8").FormulaR1C1 = _
"=SUM(Search)"
End Sub

'<<============================

Amend the worksheet name to accord with your situation and, in the following
line, change "A1" to reflect the first cell reference of your Autofilter
range.

---
Regards,
Norman



"MMH" wrote in message
...
I am writing a macro that takes data from a sheet showing amounts invoiced
in
a given month, decides what type of work was invoiced, and calclulates
totals
for the different types of work.

I have one worksheet that shows the values billed in column E, and the
type
of work billed in column F. I want to find all cells in column F with the
same type/value, then select the corresponding cells in column E, and
apply a
name to the range that I can then insert into formulas.

The code I am using to do this is shown below. The problem I have is that
if a particular term I search for is not in column F, all the rows in
column
E will be selected (which doesn't help me much).

What I would like to do is change the code to say that if there are no
matching entries in column F, no range is created. And later when I
create
my formulas, I want something to say that if a particular range doesn't
exist, the value of the cell should be zero.

Any help doing this would be appreciated.

Thanks
MMH

***

'
' Creating Name for Search range to use in later formula
'
Selection.AutoFilter Field:=6, Criteria1:="Search"
Range("A1").Select
Cells.Find(What:="Search", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase _
:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, -1).Select
Range(Selection, Selection.End(xlDown)).Name = "Search"
'
' Get values of search column from this month's workbook
'
Sheets("YTD Totals").Select
Range("E8").Select
ActiveCell.FormulaR1C1 = "=SUM(Search)"
Range("E9").Select