Can somebody check my script??

[Guest]

Hi
I have a button aon a worksheet that asks the user to select a 'job' to be
printed. after input that number is searched for in column B of 'Thursday's
log' and when this number is found the row of data is copied and pasted into
the 'formula' worksheet.
I thought I had this working, but I have copied the workbook onto another
computer at work and it doesn't seem to work.
Can anybody help, here is the script I have

Sub print_thursdays_jobcard()
Dim i As Integer
Dim iRow As Integer
Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet
Dim lLastRow As Long
On Error GoTo err_handler
Set wks1 = Worksheets("Thursday's log")
Set wks2 = Worksheets("formula")
Set wks3 = Worksheets("jobcard")
i = InputBox("Please enter the job number you wish to print a job card for")
iRow = Columns("B:B").Find _
(What:=i, _
After:=Range("B1"), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Row
Worksheets("thursday's log").Activate
ActiveSheet.Cells(iRow, 1).EntireRow.Copy Destination _
:=Worksheets("formula").Cells(2, 1)

wks3.PrintOut
Exit Sub

err_handler:
MsgBox "No job with the number " & i & " has been found, please try
again!"
End Sub


thanks

Anthony

 

[Harald Staff]

Hi Anthony

A few things here:
Sometimes you use wks2, sometimes you use Worksheets("formula").
You don't sspecify which workbook these sheets are in.
You assume that all errors are caused by the number not existing. Errors may
be caused by renamed sheets, protected sheets, missing printer, ...
An inputbox returns text. You should convert it to numeric and thereby also
trap "Cancel".

Here's my suggested first revision:

Sub print_thursdays_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("Thursday's log")
Set wks2 = ThisWorkbook.Worksheets("formula")
Set wks3 = ThisWorkbook.Worksheets("jobcard")
i = Val(InputBox("Please enter the job number you wish to print a job card
for"))
If i = 0 Then Exit Sub
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)

MsgBox "wks3.PrintOut"
Exit Sub

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

HTH. Best wishes Harald

 

[Guest]

Harald,
thanks for the help/info
it seems to work - for now !!

many thanks
Anthony

 

[Harald Staff]

Glad to hear that Anthony. Thanks for the feedback.

Best wishes Harald