Can't seem to get the syntax right to open the workbook named in the input box. In this case VMNB123 but this will vary but it will be open at the same time as the target workbook. Is this a great forum or what?
Sub ImportJob()
'Imports all data for a new job
Dim JobCode As Variant
Dim sht As Variant
On Error GoTo errhand
JobCode = InputBox(prompt:="", Title:="INPUT THE JOB CODE")
If JobCode = "" Then
MsgBox prompt:="", Title:="NOTHING ENTERED"
Exit Sub
End If
Application.ScreenUpdating = False
Windows("VMNB123.xls").Activate
For Each sht In ActiveWorkbook.Worksheets
Windows("VMNB123.xls").Activate
Range("B18:D37").Select
Selection.Copy
Range("A1").Select
Windows("Pay Verification.xls").Activate
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("B65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.Offset(19, 0)).Select
Selection.FormulaR1C1 = JobCode
Range("C65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.Offset(19, 0)).Select
Selection.FormulaR1C1 = sht.Name
Range("D65536").End(xlUp).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.Offset(19, 0)).Select
ActiveSheet.Paste
Range("H65536").End(xlUp).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.Offset(19, 0)).Select
ActiveSheet.Paste
Selection.NumberFormat = "0.00"
Application.CutCopyMode = False
Range("A1").Select
Next sht
errhand:
MsgBox prompt:="", Title:="ERROR, TRY AGAIN"
End Sub
Sub ImportJob()
'Imports all data for a new job
Dim JobCode As Variant
Dim sht As Variant
On Error GoTo errhand
JobCode = InputBox(prompt:="", Title:="INPUT THE JOB CODE")
If JobCode = "" Then
MsgBox prompt:="", Title:="NOTHING ENTERED"
Exit Sub
End If
Application.ScreenUpdating = False
Windows("VMNB123.xls").Activate
For Each sht In ActiveWorkbook.Worksheets
Windows("VMNB123.xls").Activate
Range("B18:D37").Select
Selection.Copy
Range("A1").Select
Windows("Pay Verification.xls").Activate
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("B65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.Offset(19, 0)).Select
Selection.FormulaR1C1 = JobCode
Range("C65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.Offset(19, 0)).Select
Selection.FormulaR1C1 = sht.Name
Range("D65536").End(xlUp).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.Offset(19, 0)).Select
ActiveSheet.Paste
Range("H65536").End(xlUp).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.Offset(19, 0)).Select
ActiveSheet.Paste
Selection.NumberFormat = "0.00"
Application.CutCopyMode = False
Range("A1").Select
Next sht
errhand:
MsgBox prompt:="", Title:="ERROR, TRY AGAIN"
End Sub