Hi,
I have the following code (donated) which will take a number input by the
user and then seach a whole column for that corresponding number. Once found
it will then copy/paste the whole of that rows data onto another worksheet.
This works fine as there is never the same number in the column twice, what
I now need is for the column to be searched and then each time the same
number is found to copy each rows data onto a seperate sheet until all the
'input number' corresponding rows data has been pasted onto seperate rows in
a new worksheet.

For example, if the number input by the user is 12345 . The seach is made
and its found that this number is shown in cells A3, A7, A10 I want all the
data in cells A3:G3, A7:G7, and A10:G10 to be pasted into seperate rows in
another worksheet.
hope that makes sense, and here is the code I have....

Sub print_mon_jobcard()
Dim i As Integer
Dim iRow As Integer
Dim Cel As Range
Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet
Dim lLastRow As Long
'On Error GoTo err_handler
Set wks1 = ThisWorkbook.Worksheets("monday's log")
Set wks2 = ThisWorkbook.Worksheets("formula")
Set wks3 = ThisWorkbook.Worksheets("jobcard")
i = InputBox("Please enter the job number you wish to print a job card for")

On Error Resume Next
Set Cel = wks1.Columns("B:B").Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If Cel Is Nothing Then
MsgBox "No job with the number " & i & _
" has been found, please try again! "
Exit Sub
End If
On Error GoTo err_handler
iRow = Cel.Row
wks1.Cells(iRow, 1).EntireRow.Copy Destination _
:=wks2.Cells(2, 1)

wks3.PrintOut
Exit Sub

err_handler:
MsgBox Error, , "Err " & Err.Number
End Sub

This should be close...

Sub print_mon_jobcard()
Dim i As Integer
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFirst As Range
Dim rngDestination As Range
Dim rngAllRecords As Range
Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet

'On Error GoTo err_handler
Set wks1 = ThisWorkbook.Worksheets("monday's log")
Set wks2 = ThisWorkbook.Worksheets("formula")
Set wks3 = ThisWorkbook.Worksheets("jobcard")
i = InputBox("Please enter the job number you wish to print a job card for")

On Error Resume Next
Set rngToSearch = wks1.Columns("B")
Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

Set rngFound = rngToSearch.Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If rngFound Is Nothing Then
MsgBox "No job with the number " & i & _
" has been found, please try again! "
Else
On Error GoTo err_handler
Set rngFirst = rngFound
Set rngAllRecords = rngFound
Do
Set rngAllRecords = Union(rngAllRecords, rngFound)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = rngFirst.Address
rngAllRecords.Entirerow.Copy rngDestination
wks3.PrintOut
End If
Exit Sub

err_handler:
MsgBox Error, , "Err " & Err.Number
End Sub
--
HTH...

Jim Thomlinson


"Anthony" wrote:

Hi,
I have the following code (donated) which will take a number input by the
user and then seach a whole column for that corresponding number. Once found
it will then copy/paste the whole of that rows data onto another worksheet.
This works fine as there is never the same number in the column twice, what
I now need is for the column to be searched and then each time the same
number is found to copy each rows data onto a seperate sheet until all the
'input number' corresponding rows data has been pasted onto seperate rows in
a new worksheet.

For example, if the number input by the user is 12345 . The seach is made
and its found that this number is shown in cells A3, A7, A10 I want all the
data in cells A3:G3, A7:G7, and A10:G10 to be pasted into seperate rows in
another worksheet.
hope that makes sense, and here is the code I have....

Sub print_mon_jobcard()
Dim i As Integer
Dim iRow As Integer
Dim Cel As Range
Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet
Dim lLastRow As Long
'On Error GoTo err_handler
Set wks1 = ThisWorkbook.Worksheets("monday's log")
Set wks2 = ThisWorkbook.Worksheets("formula")
Set wks3 = ThisWorkbook.Worksheets("jobcard")
i = InputBox("Please enter the job number you wish to print a job card for")

On Error Resume Next
Set Cel = wks1.Columns("B:B").Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If Cel Is Nothing Then
MsgBox "No job with the number " & i & _
" has been found, please try again! "
Exit Sub
End If
On Error GoTo err_handler
iRow = Cel.Row
wks1.Cells(iRow, 1).EntireRow.Copy Destination _
:=wks2.Cells(2, 1)

wks3.PrintOut
Exit Sub

err_handler:
MsgBox Error, , "Err " & Err.Number
End Sub

Jim,
Thanks - I'll give that a go and let you know
many thanks

"Jim Thomlinson" wrote:

This should be close...

Sub print_mon_jobcard()
Dim i As Integer
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFirst As Range
Dim rngDestination As Range
Dim rngAllRecords As Range
Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet

'On Error GoTo err_handler
Set wks1 = ThisWorkbook.Worksheets("monday's log")
Set wks2 = ThisWorkbook.Worksheets("formula")
Set wks3 = ThisWorkbook.Worksheets("jobcard")
i = InputBox("Please enter the job number you wish to print a job card for")

On Error Resume Next
Set rngToSearch = wks1.Columns("B")
Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

Set rngFound = rngToSearch.Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If rngFound Is Nothing Then
MsgBox "No job with the number " & i & _
" has been found, please try again! "
Else
On Error GoTo err_handler
Set rngFirst = rngFound
Set rngAllRecords = rngFound
Do
Set rngAllRecords = Union(rngAllRecords, rngFound)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = rngFirst.Address
rngAllRecords.Entirerow.Copy rngDestination
wks3.PrintOut
End If
Exit Sub

err_handler:
MsgBox Error, , "Err " & Err.Number
End Sub
--
HTH...

Jim Thomlinson


"Anthony" wrote:

Hi,
I have the following code (donated) which will take a number input by the
user and then seach a whole column for that corresponding number. Once found
it will then copy/paste the whole of that rows data onto another worksheet.
This works fine as there is never the same number in the column twice, what
I now need is for the column to be searched and then each time the same
number is found to copy each rows data onto a seperate sheet until all the
'input number' corresponding rows data has been pasted onto seperate rows in
a new worksheet.

For example, if the number input by the user is 12345 . The seach is made
and its found that this number is shown in cells A3, A7, A10 I want all the
data in cells A3:G3, A7:G7, and A10:G10 to be pasted into seperate rows in
another worksheet.
hope that makes sense, and here is the code I have....

Sub print_mon_jobcard()
Dim i As Integer
Dim iRow As Integer
Dim Cel As Range
Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet
Dim lLastRow As Long
'On Error GoTo err_handler
Set wks1 = ThisWorkbook.Worksheets("monday's log")
Set wks2 = ThisWorkbook.Worksheets("formula")
Set wks3 = ThisWorkbook.Worksheets("jobcard")
i = InputBox("Please enter the job number you wish to print a job card for")

On Error Resume Next
Set Cel = wks1.Columns("B:B").Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If Cel Is Nothing Then
MsgBox "No job with the number " & i & _
" has been found, please try again! "
Exit Sub
End If
On Error GoTo err_handler
iRow = Cel.Row
wks1.Cells(iRow, 1).EntireRow.Copy Destination _
:=wks2.Cells(2, 1)

wks3.PrintOut
Exit Sub

err_handler:
MsgBox Error, , "Err " & Err.Number
End Sub

I have always felt, best to teach how to fish, rather than give a fish. What
it sounds like you want to do is take data from sheet1 and place it on
sheet2. For every occurrence of the search data in sheet1, add it to the
list of items on sheet2. (Not replace the data on sheet2.) Because the
location to store the data on sheet2 will change, you need to keep track of
it somehow. (Variable, like 'long DestRowNum') Everytime you make a match,
copy the data and increase DestRowNum by one. As for the search, there is an
Excel VBA example. The example stores the row of the first search response,
then repeats the search until the row returned equals the first response.

Course, sometimes it's easier to just to lead a horse near the water. :)

"Anthony" wrote:

Jim,
Thanks - I'll give that a go and let you know
many thanks

"Jim Thomlinson" wrote:

This should be close...

Sub print_mon_jobcard()
Dim i As Integer
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFirst As Range
Dim rngDestination As Range
Dim rngAllRecords As Range
Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet

'On Error GoTo err_handler
Set wks1 = ThisWorkbook.Worksheets("monday's log")
Set wks2 = ThisWorkbook.Worksheets("formula")
Set wks3 = ThisWorkbook.Worksheets("jobcard")
i = InputBox("Please enter the job number you wish to print a job card for")

On Error Resume Next
Set rngToSearch = wks1.Columns("B")
Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

Set rngFound = rngToSearch.Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If rngFound Is Nothing Then
MsgBox "No job with the number " & i & _
" has been found, please try again! "
Else
On Error GoTo err_handler
Set rngFirst = rngFound
Set rngAllRecords = rngFound
Do
Set rngAllRecords = Union(rngAllRecords, rngFound)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = rngFirst.Address
rngAllRecords.Entirerow.Copy rngDestination
wks3.PrintOut
End If
Exit Sub

err_handler:
MsgBox Error, , "Err " & Err.Number
End Sub
--
HTH...

Jim Thomlinson


"Anthony" wrote:

Hi,
I have the following code (donated) which will take a number input by the
user and then seach a whole column for that corresponding number. Once found
it will then copy/paste the whole of that rows data onto another worksheet.
This works fine as there is never the same number in the column twice, what
I now need is for the column to be searched and then each time the same
number is found to copy each rows data onto a seperate sheet until all the
'input number' corresponding rows data has been pasted onto seperate rows in
a new worksheet.

For example, if the number input by the user is 12345 . The seach is made
and its found that this number is shown in cells A3, A7, A10 I want all the
data in cells A3:G3, A7:G7, and A10:G10 to be pasted into seperate rows in
another worksheet.
hope that makes sense, and here is the code I have....

Sub print_mon_jobcard()
Dim i As Integer
Dim iRow As Integer
Dim Cel As Range
Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet
Dim lLastRow As Long
'On Error GoTo err_handler
Set wks1 = ThisWorkbook.Worksheets("monday's log")
Set wks2 = ThisWorkbook.Worksheets("formula")
Set wks3 = ThisWorkbook.Worksheets("jobcard")
i = InputBox("Please enter the job number you wish to print a job card for")

On Error Resume Next
Set Cel = wks1.Columns("B:B").Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If Cel Is Nothing Then
MsgBox "No job with the number " & i & _
" has been found, please try again! "
Exit Sub
End If
On Error GoTo err_handler
iRow = Cel.Row
wks1.Cells(iRow, 1).EntireRow.Copy Destination _
:=wks2.Cells(2, 1)

wks3.PrintOut
Exit Sub

err_handler:
MsgBox Error, , "Err " & Err.Number
End Sub

GB,
thanks for your 'wise' words, and yes that is exactly what I want.
The reason I posted here is becasue I am a novice at VB code, and to write
something to do as I requested would take me a lifetime.
any donations, therefore wud be very much apreciated!
rgds

"GB" wrote:

I have always felt, best to teach how to fish, rather than give a fish. What
it sounds like you want to do is take data from sheet1 and place it on
sheet2. For every occurrence of the search data in sheet1, add it to the
list of items on sheet2. (Not replace the data on sheet2.) Because the
location to store the data on sheet2 will change, you need to keep track of
it somehow. (Variable, like 'long DestRowNum') Everytime you make a match,
copy the data and increase DestRowNum by one. As for the search, there is an
Excel VBA example. The example stores the row of the first search response,
then repeats the search until the row returned equals the first response.

Course, sometimes it's easier to just to lead a horse near the water. :)

"Anthony" wrote:

Jim,
Thanks - I'll give that a go and let you know
many thanks

"Jim Thomlinson" wrote:

This should be close...

Sub print_mon_jobcard()
Dim i As Integer
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFirst As Range
Dim rngDestination As Range
Dim rngAllRecords As Range
Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet

'On Error GoTo err_handler
Set wks1 = ThisWorkbook.Worksheets("monday's log")
Set wks2 = ThisWorkbook.Worksheets("formula")
Set wks3 = ThisWorkbook.Worksheets("jobcard")
i = InputBox("Please enter the job number you wish to print a job card for")

On Error Resume Next
Set rngToSearch = wks1.Columns("B")
Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

Set rngFound = rngToSearch.Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If rngFound Is Nothing Then
MsgBox "No job with the number " & i & _
" has been found, please try again! "
Else
On Error GoTo err_handler
Set rngFirst = rngFound
Set rngAllRecords = rngFound
Do
Set rngAllRecords = Union(rngAllRecords, rngFound)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = rngFirst.Address
rngAllRecords.Entirerow.Copy rngDestination
wks3.PrintOut
End If
Exit Sub

err_handler:
MsgBox Error, , "Err " & Err.Number
End Sub
--
HTH...

Jim Thomlinson


"Anthony" wrote:

Hi,
I have the following code (donated) which will take a number input by the
user and then seach a whole column for that corresponding number. Once found
it will then copy/paste the whole of that rows data onto another worksheet.
This works fine as there is never the same number in the column twice, what
I now need is for the column to be searched and then each time the same
number is found to copy each rows data onto a seperate sheet until all the
'input number' corresponding rows data has been pasted onto seperate rows in
a new worksheet.

For example, if the number input by the user is 12345 . The seach is made
and its found that this number is shown in cells A3, A7, A10 I want all the
data in cells A3:G3, A7:G7, and A10:G10 to be pasted into seperate rows in
another worksheet.
hope that makes sense, and here is the code I have....

Sub print_mon_jobcard()
Dim i As Integer
Dim iRow As Integer
Dim Cel As Range
Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet
Dim lLastRow As Long
'On Error GoTo err_handler
Set wks1 = ThisWorkbook.Worksheets("monday's log")
Set wks2 = ThisWorkbook.Worksheets("formula")
Set wks3 = ThisWorkbook.Worksheets("jobcard")
i = InputBox("Please enter the job number you wish to print a job card for")

On Error Resume Next
Set Cel = wks1.Columns("B:B").Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If Cel Is Nothing Then
MsgBox "No job with the number " & i & _
" has been found, please try again! "
Exit Sub
End If
On Error GoTo err_handler
iRow = Cel.Row
wks1.Cells(iRow, 1).EntireRow.Copy Destination _
:=wks2.Cells(2, 1)

wks3.PrintOut
Exit Sub

err_handler:
MsgBox Error, , "Err " & Err.Number
End Sub

So did Jim's solution fix your problem, or are you still fishing? :)

"Anthony" wrote:

GB,
thanks for your 'wise' words, and yes that is exactly what I want.
The reason I posted here is becasue I am a novice at VB code, and to write
something to do as I requested would take me a lifetime.
any donations, therefore wud be very much apreciated!
rgds

"GB" wrote:

I have always felt, best to teach how to fish, rather than give a fish. What
it sounds like you want to do is take data from sheet1 and place it on
sheet2. For every occurrence of the search data in sheet1, add it to the
list of items on sheet2. (Not replace the data on sheet2.) Because the
location to store the data on sheet2 will change, you need to keep track of
it somehow. (Variable, like 'long DestRowNum') Everytime you make a match,
copy the data and increase DestRowNum by one. As for the search, there is an
Excel VBA example. The example stores the row of the first search response,
then repeats the search until the row returned equals the first response.

Course, sometimes it's easier to just to lead a horse near the water. :)

"Anthony" wrote:

Jim,
Thanks - I'll give that a go and let you know
many thanks

"Jim Thomlinson" wrote:

This should be close...

Sub print_mon_jobcard()
Dim i As Integer
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFirst As Range
Dim rngDestination As Range
Dim rngAllRecords As Range
Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet

'On Error GoTo err_handler
Set wks1 = ThisWorkbook.Worksheets("monday's log")
Set wks2 = ThisWorkbook.Worksheets("formula")
Set wks3 = ThisWorkbook.Worksheets("jobcard")
i = InputBox("Please enter the job number you wish to print a job card for")

On Error Resume Next
Set rngToSearch = wks1.Columns("B")
Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

Set rngFound = rngToSearch.Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If rngFound Is Nothing Then
MsgBox "No job with the number " & i & _
" has been found, please try again! "
Else
On Error GoTo err_handler
Set rngFirst = rngFound
Set rngAllRecords = rngFound
Do
Set rngAllRecords = Union(rngAllRecords, rngFound)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = rngFirst.Address
rngAllRecords.Entirerow.Copy rngDestination
wks3.PrintOut
End If
Exit Sub

err_handler:
MsgBox Error, , "Err " & Err.Number
End Sub
--
HTH...

Jim Thomlinson


"Anthony" wrote:

Hi,
I have the following code (donated) which will take a number input by the
user and then seach a whole column for that corresponding number. Once found
it will then copy/paste the whole of that rows data onto another worksheet.
This works fine as there is never the same number in the column twice, what
I now need is for the column to be searched and then each time the same
number is found to copy each rows data onto a seperate sheet until all the
'input number' corresponding rows data has been pasted onto seperate rows in
a new worksheet.

For example, if the number input by the user is 12345 . The seach is made
and its found that this number is shown in cells A3, A7, A10 I want all the
data in cells A3:G3, A7:G7, and A10:G10 to be pasted into seperate rows in
another worksheet.
hope that makes sense, and here is the code I have....

Sub print_mon_jobcard()
Dim i As Integer
Dim iRow As Integer
Dim Cel As Range
Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet
Dim lLastRow As Long
'On Error GoTo err_handler
Set wks1 = ThisWorkbook.Worksheets("monday's log")
Set wks2 = ThisWorkbook.Worksheets("formula")
Set wks3 = ThisWorkbook.Worksheets("jobcard")
i = InputBox("Please enter the job number you wish to print a job card for")

On Error Resume Next
Set Cel = wks1.Columns("B:B").Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If Cel Is Nothing Then
MsgBox "No job with the number " & i & _
" has been found, please try again! "
Exit Sub
End If
On Error GoTo err_handler
iRow = Cel.Row
wks1.Cells(iRow, 1).EntireRow.Copy Destination _
:=wks2.Cells(2, 1)

wks3.PrintOut
Exit Sub

err_handler:
MsgBox Error, , "Err " & Err.Number
End Sub

Ohh and no problem. I think this area is great for novices and experts
alike. It's amazing the kind of help that can be found and even the kind of
errors that can be pointed out by almost anyone, from a misplaced colon to an
extra parantheses. At any rate, I often see people go for the code, and like
to back it up with some understanding, or vice versa. Sometimes people
really know the code but don't understand what they need to do to get where
they want. *shrug*

There are some very helpful people on here. I sometimes wonder what they do
for their day job, that they can be here providing such great help so often.
But then again, look at me. :)

"Anthony" wrote:

GB,
thanks for your 'wise' words, and yes that is exactly what I want.
The reason I posted here is becasue I am a novice at VB code, and to write
something to do as I requested would take me a lifetime.
any donations, therefore wud be very much apreciated!
rgds

"GB" wrote:

I have always felt, best to teach how to fish, rather than give a fish. What
it sounds like you want to do is take data from sheet1 and place it on
sheet2. For every occurrence of the search data in sheet1, add it to the
list of items on sheet2. (Not replace the data on sheet2.) Because the
location to store the data on sheet2 will change, you need to keep track of
it somehow. (Variable, like 'long DestRowNum') Everytime you make a match,
copy the data and increase DestRowNum by one. As for the search, there is an
Excel VBA example. The example stores the row of the first search response,
then repeats the search until the row returned equals the first response.

Course, sometimes it's easier to just to lead a horse near the water. :)

"Anthony" wrote:

Jim,
Thanks - I'll give that a go and let you know
many thanks

"Jim Thomlinson" wrote:

This should be close...

Sub print_mon_jobcard()
Dim i As Integer
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFirst As Range
Dim rngDestination As Range
Dim rngAllRecords As Range
Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet

'On Error GoTo err_handler
Set wks1 = ThisWorkbook.Worksheets("monday's log")
Set wks2 = ThisWorkbook.Worksheets("formula")
Set wks3 = ThisWorkbook.Worksheets("jobcard")
i = InputBox("Please enter the job number you wish to print a job card for")

On Error Resume Next
Set rngToSearch = wks1.Columns("B")
Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

Set rngFound = rngToSearch.Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If rngFound Is Nothing Then
MsgBox "No job with the number " & i & _
" has been found, please try again! "
Else
On Error GoTo err_handler
Set rngFirst = rngFound
Set rngAllRecords = rngFound
Do
Set rngAllRecords = Union(rngAllRecords, rngFound)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = rngFirst.Address
rngAllRecords.Entirerow.Copy rngDestination
wks3.PrintOut
End If
Exit Sub

err_handler:
MsgBox Error, , "Err " & Err.Number
End Sub
--
HTH...

Jim Thomlinson


"Anthony" wrote:

Hi,
I have the following code (donated) which will take a number input by the
user and then seach a whole column for that corresponding number. Once found
it will then copy/paste the whole of that rows data onto another worksheet.
This works fine as there is never the same number in the column twice, what
I now need is for the column to be searched and then each time the same
number is found to copy each rows data onto a seperate sheet until all the
'input number' corresponding rows data has been pasted onto seperate rows in
a new worksheet.

For example, if the number input by the user is 12345 . The seach is made
and its found that this number is shown in cells A3, A7, A10 I want all the
data in cells A3:G3, A7:G7, and A10:G10 to be pasted into seperate rows in
another worksheet.
hope that makes sense, and here is the code I have....

Sub print_mon_jobcard()
Dim i As Integer
Dim iRow As Integer
Dim Cel As Range
Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet
Dim lLastRow As Long
'On Error GoTo err_handler
Set wks1 = ThisWorkbook.Worksheets("monday's log")
Set wks2 = ThisWorkbook.Worksheets("formula")
Set wks3 = ThisWorkbook.Worksheets("jobcard")
i = InputBox("Please enter the job number you wish to print a job card for")

On Error Resume Next
Set Cel = wks1.Columns("B:B").Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If Cel Is Nothing Then
MsgBox "No job with the number " & i & _
" has been found, please try again! "
Exit Sub
End If
On Error GoTo err_handler
iRow = Cel.Row
wks1.Cells(iRow, 1).EntireRow.Copy Destination _
:=wks2.Cells(2, 1)

wks3.PrintOut
Exit Sub

err_handler:
MsgBox Error, , "Err " & Err.Number
End Sub