try this sub- do you get what you want
===============
Public Sub test()
Dim i As Single
i = InputBox("type the number")
Worksheets("sheet1").Activate
On Error GoTo line1
ActiveSheet.Columns("a:a").Cells.Find(i, LookIn:=xlValue,
after:=Range("a1")).Activate
ActiveCell.EntireRow.Copy Destination _
:=Worksheets("sheet2").Range("a2")
Worksheets("sheet2").Activate
Range("a1").Select
GoTo line2
line1: MsgBox "the particular number is available"
line2:
End Sub
==============
if you have more than one such number in the columnA this macro picks up the
first availabale number after A1. which row do you want to copy
in that case may need some modification to sub
-
remove $$$ from email addresss to send email
================================================== =
Anthony wrote in message
...
Thanks to those who offered help but I still am not quite there with the
correct solution.
R.Venkataraman's code was almost correct but I don't think I explained
myself correctly.
In my sheet1 the user inputs numbers (1-50) in column A.(pluss loads of
other data in columns B:P) what I require is the macro to ask the user to
input the 'job' they want to print. So if the user inputs <9 then column
A
in sheet 1 is searched for the number 9 then the contents of that
particular
row are copied (without formula - just cell values) and the data pasted
onto
row A2 of sheet 2.
Each time the button is selected the data is always pasted to the same
cell
ref in sheet 2, thus overwriting any data already there.
The code I have so far is this,
Sub test()
Dim i As Integer
Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet
Dim lLastRow As Long
On Error GoTo err_handler
Set wks1 = Worksheets("sheet1")
Set wks2 = Worksheets("sheet2")
Set wks3 = Worksheets("sheet3")
i = InputBox("type the row number desired")
Worksheets("sheet1").Activate
ActiveSheet.Cells(i, 1).EntireRow.Copy Destination _
:=Worksheets("sheet2").Cells(i, 1)
wks3.PrintOut preview:=True
Exit Sub
err_handler:
MsgBox "An error has ocurred, please try again"
End Sub
but if the user enters <9 as his/her selected request row A9 is copied
and
pasted. I want column A to be searched for the number <9 then whatever
row
this is, (any row from A5:A1000) copy this whole row and paste it into A2
of
sheet 2.
Sorry for the waffle but please can anybody help, as my head hurts!!!
many thanks
Anthony
|