Need Help with Offset

[mrdata]

I am trying to get this to work I have my data laid out like this

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

I am searching the jobcode column for a jobcode say 45465
I want to copy that jobcode and job title as well as the employee data
to a new worksheet list I can select what I need using this code

This code almost works
Worksheets("Data Entry").Activate
ActiveCell.Offset(rowOffset:=-2, columnOffset:=-5).Activate
ActiveCell.Range("A1:E1").Select

it Only Works if I find the 3rd code down 45465
But if I search for the second or the first codes the offset continues
to move up.
How can I make the offset find the employee data each time as well as
the jobcode and job title?

Each employee is listed like the above.

Please forgive the '''''''' it's the only way I can get the layout to
stay right

Thanks
Charles

 

[Jim Thomlinson]

Try this...

Worksheets("Data Entry").Activate
ActiveCell.Offset(0, -5).End(xlUp).Select

 

[mrdata]

It works for any rows below the first set of rows.
The first three rows under the header row it selects the header ro
instead.
Also I need it to select Column B,C,D,E of the Employee Data as well.

Getting there good job!

Thanks
Charle

 

[mrdata]

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