Now how do I make it copy the data after it finds it. It will need to
search the jobcode column.

Emp # Name '''''''''' Dept# '''''Shift'''Hire
Date'''''''''Jobcode''''''Jobtitle
121 '''''John Doe'''''' 2525 '''''''''' A''''''07/08/06'''''''' 56254
''''''''''' Job1
''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''' 25648 ''''''''''''Job2
''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''' 45465 ''''''''''''Job3

And copy the Jobcode ,Job Title and the employee data for each employee
it finds matching the search.
How can I merge this ActiveCell.Offset(0, -5).End(xlUp).Select

To work with the following code?
This code only copys the row data adjacent to the search string (Job
Code)
Thus I get Blank feilds up to the jobcode and job title which are
copied just fine.
The only way around this is to have the persons name listed three times
one for each jobcode.
I would like to have the data laid out as above instead.

On Error GoTo Fixit:
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim Str As String
Dim sqyds
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Prebid Job List").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Dim Message, Title, Default, Mydate
Message = "Enter a Job Code For the Open Position Report ie:830134"
' Set prompt.
Title = "Open Position Pre-Bid Search" ' Set title.

Sname = InputBox(Message, Title)
Set WS = Sheets("Data Entry")
Set rng = WS.Range("A4:G4").CurrentRegion
Str = "Prebid Job List"

'Close AutoFilter first
WS.AutoFilterMode = False
rng.AutoFilter Field:=6, Criteria1:=Str
rng.AutoFilter Field:=6, Criteria1:=Sname

Set WSNew = Worksheets.Add
WS.AutoFilter.Range.Copy

With WSNew.Range("A6")
..PasteSpecial Paste:=8
..PasteSpecial xlPasteValues
..PasteSpecial xlPasteFormats
Application.CutCopyMode = False
..Select
End With
WS.AutoFilterMode = False


--
mrdata
------------------------------------------------------------------------
mrdata's Profile: http://www.excelforum.com/member.php...o&userid=17899
View this thread: http://www.excelforum.com/showthread...hreadid=560096