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

Anthony

try:

Sub test()
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("sheet1")
Set wks2 = Worksheets("sheet2")
Set wks3 = Worksheets("sheet3")
i = InputBox("type the row number desired")
iRow = Columns("A:A").Find _
(What:=i, _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Row
Worksheets("sheet1").Activate
ActiveSheet.Cells(iRow, 1).EntireRow.Copy Destination _
:=Worksheets("sheet2").Cells(2, 1)

wks3.PrintOut preview:=True
Exit Sub

err_handler:
MsgBox "An error has ocurred, please try again"
End Sub

Regards

Trevor


"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

Trevor,
Your reply works, I will have to tweak it a little (hope it works as I'm a
novice), but thanks so much as this is the final piece of the jigsaw in my
workbook.

Many thanks,

"Trevor Shuttleworth" wrote:

Anthony

try:

Sub test()
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("sheet1")
Set wks2 = Worksheets("sheet2")
Set wks3 = Worksheets("sheet3")
i = InputBox("type the row number desired")
iRow = Columns("A:A").Find _
(What:=i, _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Row
Worksheets("sheet1").Activate
ActiveSheet.Cells(iRow, 1).EntireRow.Copy Destination _
:=Worksheets("sheet2").Cells(2, 1)

wks3.PrintOut preview:=True
Exit Sub

err_handler:
MsgBox "An error has ocurred, please try again"
End Sub

Regards

Trevor


"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

Anthony

<<Your reply works ... I knew that, but you're very welcome. How so
<<tweak it a little, it does what you said you wanted ... find the row and
copy it to row 2 on sheet 2. If you need any more help, post back.

Good luck with your workbook.

Regards

Trevor


"Anthony" wrote in message
...
Trevor,
Your reply works, I will have to tweak it a little (hope it works as I'm a
novice), but thanks so much as this is the final piece of the jigsaw in my
workbook.

Many thanks,

"Trevor Shuttleworth" wrote:

Anthony

try:

Sub test()
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("sheet1")
Set wks2 = Worksheets("sheet2")
Set wks3 = Worksheets("sheet3")
i = InputBox("type the row number desired")
iRow = Columns("A:A").Find _
(What:=i, _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Row
Worksheets("sheet1").Activate
ActiveSheet.Cells(iRow, 1).EntireRow.Copy Destination _
:=Worksheets("sheet2").Cells(2, 1)

wks3.PrintOut preview:=True
Exit Sub

err_handler:
MsgBox "An error has ocurred, please try again"
End Sub

Regards

Trevor


"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

Trevor,
By tweak I ment just change the "sheet" names and wording on the message
boxes, oh and add the number input by the user to the error message box.
This I have done and it does all work, so thanks again
You shud be a MVP !!
rgds
Anthony

"Trevor Shuttleworth" wrote:

Anthony

<<Your reply works ... I knew that, but you're very welcome. How so
<<tweak it a little, it does what you said you wanted ... find the row and
copy it to row 2 on sheet 2. If you need any more help, post back.

Good luck with your workbook.

Regards

Trevor


"Anthony" wrote in message
...
Trevor,
Your reply works, I will have to tweak it a little (hope it works as I'm a
novice), but thanks so much as this is the final piece of the jigsaw in my
workbook.

Many thanks,

"Trevor Shuttleworth" wrote:

Anthony

try:

Sub test()
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("sheet1")
Set wks2 = Worksheets("sheet2")
Set wks3 = Worksheets("sheet3")
i = InputBox("type the row number desired")
iRow = Columns("A:A").Find _
(What:=i, _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Row
Worksheets("sheet1").Activate
ActiveSheet.Cells(iRow, 1).EntireRow.Copy Destination _
:=Worksheets("sheet2").Cells(2, 1)

wks3.PrintOut preview:=True
Exit Sub

err_handler:
MsgBox "An error has ocurred, please try again"
End Sub

Regards

Trevor


"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

Thanks ... I wish! But there's much more talent and experience out there
than I've got ... I'm still learning

Regards

Trevor


"Anthony" wrote in message
...
Trevor,
By tweak I ment just change the "sheet" names and wording on the message
boxes, oh and add the number input by the user to the error message box.
This I have done and it does all work, so thanks again
You shud be a MVP !!
rgds
Anthony

"Trevor Shuttleworth" wrote:

Anthony

<<Your reply works ... I knew that, but you're very welcome. How so
<<tweak it a little, it does what you said you wanted ... find the row
and
copy it to row 2 on sheet 2. If you need any more help, post back.

Good luck with your workbook.

Regards

Trevor


"Anthony" wrote in message
...
Trevor,
Your reply works, I will have to tweak it a little (hope it works as
I'm a
novice), but thanks so much as this is the final piece of the jigsaw in
my
workbook.

Many thanks,

"Trevor Shuttleworth" wrote:

Anthony

try:

Sub test()
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("sheet1")
Set wks2 = Worksheets("sheet2")
Set wks3 = Worksheets("sheet3")
i = InputBox("type the row number desired")
iRow = Columns("A:A").Find _
(What:=i, _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Row
Worksheets("sheet1").Activate
ActiveSheet.Cells(iRow, 1).EntireRow.Copy Destination _
:=Worksheets("sheet2").Cells(2, 1)

wks3.PrintOut preview:=True
Exit Sub

err_handler:
MsgBox "An error has ocurred, please try again"
End Sub

Regards

Trevor


"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

Hi Anthony,

You don't need a macro for it. You can do it by using worksheet functions.
What you are trying to do is, you want to fetch data in a database by using
an index right? That is easy. Use this formula:

=INDIRECT("G" & MATCH(A1,F:F,0))

F:F is the index column, and G contains data.

Why use macros when it can be done using worksheet functions.... with all
the security warnings?

Shafiee.

You can do it with macro too... Try recording it. That is the easiest way.
And then you can modify it so that the user won't see cells being selected by
the macro. Here is what you have to do.

Select Tools - Macros - Record New Macro and then click ok
Select the index column
Press Ctrl + F
Type an index number
Click the stop button


By doing that, you'll get a macro like this:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 2/28/2005 by Shaafee
'

'
Columns("A:A").Select
Selection.Find(What:="4", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
End Sub


Now change the macro like this:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 2/28/2005 by Shaafee
'

'

Dim idx
idx = Sheets("Sheet1").Columns("A:A").Find(What:=InputBo x("Please enter
the index number", "Index Prompt", 1), After:=Range("A1"),
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Value
MsgBox Sheets("Sheet1").Range("B" & idx).Value
Set idx = Nothing
End Sub


Now you might want to put the data in another cell of another sheet.
Just change the second last line to:

Sheets("Sheet2").Range("A1").Value = Sheets("Sheet1").Range("B" & idx).Value

Hi Trevor,

Just wanted to fine tune your macro..... :D
Your macro will work faster now.


Sub test()
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("sheet1")
Set wks2 = Worksheets("sheet2")
Set wks3 = Worksheets("sheet3")
i = InputBox("type the row number desired")
iRow = wks1.Columns("A:A").Find _
(What:=i, _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Row
Worksheets("sheet1").Cells(iRow, 1).EntireRow.Copy Destination _
:=Worksheets("sheet2").Cells(2, 1)

wks3.PrintOut preview:=True
Exit Sub

err_handler:
MsgBox "An error has ocurred, please try again"
End Sub


Shafiee.