Copy Variable Range

[RoodyJ]

I have data on one worksheet that I want to selectively copy to another worksheet. The data is always ten rows but the beginning row and column will be different each time the selection is made.

Inpt = InputBox("Which PO#?")

'Find the first line with the PO number
Range("A3").Select
ActiveCell(3, 1).Select
Cells.Find(What:=Inpt, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
'and copy the PO number
Selection.Copy
'to a cell on the PO Form
Sheets("PO Form").Select
Range("F2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
The above does what I want for a single cell. However, what I would like to do is copy a ten row by one column block starting at the 'found cell'. I'm not at all sure how to specify that range based on the 'found cell' results above. Any help would surely be appreciated.:help

Jim

[Simon Lloyd]

Try This

Dim Ac, AcO As String
Ac = ActiveCell.Address'''these 3 lines go after the find but before Selection.copy
AcO = ActiveCell.Offset(9, 0).Address
Range(Ac & ":" & AcO).Select

[RoodyJ]

Thanks for the input Simon. I've added the snippet, but now I get an error on running the sub: "Select method of Range class failed."

Error occurs at Range(C20") select statement ....

Private Sub POP_Click()
Dim Ac, AcO As String
Inpt = InputBox("Which PO#?")


'Go back and Find the first line with the PO number
Range("A3").Select
ActiveCell(3, 1).Select
Cells.Find(What:=Inpt, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
'and copy the Customers to the PO form
Ac = ActiveCell.Address '''these 3 lines go after the find but before Selection.copy
' offset to Customer column
Ac = ActiveCell.Offset(0, 3).Address
AcO = ActiveCell.Offset(9, 0).Address
Range(Ac & ":" & AcO).Select

Selection.Copy
Sheets("PO Form").Select
Range("C20").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Store Log").Select

End Sub

[Simon Lloyd]

you probably get a problem because i only gave you 3 of these lines!
Ac = ActiveCell.Address '''these 3 lines go after the find but before
Ac = ActiveCell.Offset(0, 3).Address
AcO = ActiveCell.Offset(9, 0).Address
Range(Ac & ":" & AcO).Select
you need to remove the first Ac= statment because you have added your own!, you also do not need to select the next sheet to paste to...why not use:

With Sheets("PO Form").Range("C20")
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With

[RoodyJ]

Simon,

After playing with this for quite awhile last night, I found that if I move the routine to a Module and call it from my button_click() Private sub, I get no errors. Not sure why this works, but it does.

I'll take your other notes and integrate the changes. Thank you once again for your help on this.

Jim