It's looking for the value of "SIDLook" in column 1. Is that what you want?
If not, you'll need to come and tell us what you want.

I'm going to assume for a minute that SIDLook is a named range referring to
a cell somewhere. If this is what you want, you'll need to change some of
your code.

rng.AutoFilter Field:=1, Criteria1:=Range("SIDLook").value

--
HTH,
Barb Reinhardt




"NoodNutt" wrote:

G'day everyone

For the life of me, I have no idea why this doesn't work properly.

All I get returning is the first record

I know this workes when I specify the Criteria1="081001", but when I use the
same value via SIDLook Name Range

Sub Search_Copy_Student()

Dim SourceSheet As Worksheet
Dim DestinationSheet As Worksheet
Dim rng As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Sheets("Exam Results").Select

Set SourceSheet = Sheets("Exam Results")
Set rng = SourceSheet.Range("A2:H" & Rows.Count)
Set DestinationSheet = Sheets("Student Select")

SourceSheet.AutoFilterMode = False
rng.AutoFilter Field:=1, Criteria1:="SIDLook"

SourceSheet.AutoFilter.Range.Copy
DestinationSheet.Select
Range("C2").Select
Selection.PasteSpecial xlPasteValues
Application.CutCopyMode = False

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

TIA
Mark.