This looks for a range named BUAffl

Maybe...

Set rngSrc = union(Range("BU"),range("Affl"))



shternm wrote:

Can some please explain to me why I am getting 'Range' of object
'_Global' failed error?

The ranges that are referred to on that line are 3 cell columns.

Thank you, thank you, thank you ………

Sub Macro2()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim rngToSearch As Range
Dim wks As Worksheet
Dim rngFound As Range
Dim Value As Variant
Dim i As Long
Dim rngSrc As Range
Dim nameToFind As String

Set wks = Sheets("Query")
Set rngToSearch = wks.Columns(1)
Set rngSrc = Range("BU" & "Affl")
----------------------------------- Error Line

Sheets("Query").Select
Range("A2").Select

' leave header row alone
For i = 2 To rngSrc.Rows.Count
' search values in Column A
nameToFind = rngSrc.Cells(i, 1).Value
If (Len(nameToFind) 0) Then
Set rngFound = rngToSearch.Find(what:=nameToFind,
lookat:=xlWhole)

Worksheets("Query").Rows("1:1").Copy
Sheets.Add
ActiveSheet.Name = nameToFind
ActiveSheet.Paste

If rngFound Is Nothing Then
Sheets("Data").Select
Exit For
Else
Do
rngFound.EntireRow.Copy
Worksheets(nameToFind).Select
ActiveCell.Offset(1, 0).Activate
ActiveSheet.Paste

rngFound.ClearContents
Set rngFound = rngToSearch.FindNext
Loop Until rngFound Is Nothing
End If
End If
Next i
End Sub

--
shternm
------------------------------------------------------------------------
shternm's Profile: http://www.excelforum.com/member.php...nfo&userid=858
View this thread: http://www.excelforum.com/showthread...hreadid=472191

--

Dave Peterson