Change that last Activesheet.Paste to
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Eduardo" wrote:

John, it works but I forgot to mention that the range J10 to P contains
formula how do I paste as value
Thank you

"John Bundy" wrote:

This should do it for you, you may have to modify a bit to go with the rest
of your code
Dim lastRow As Long
lastRow = Cells(Rows.Count, "P").End(xlUp).Row
'set variable to the last used row in P
Range("J10:P" & lastRow).Copy

Sheets("Sheet2").Select
Sheets("Sheet2").Range("C17").Select
ActiveSheet.Paste
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Eduardo" wrote:

I have 5 stores sending their orders in an excel spreadsheet, there it is
where depending on the items ordered the range will vary. So what I need is
to select the range from J10 to the last row used in "P" and copy it into
cell C17 in Sheet2

"John Bundy" wrote:

Shouldn't be a problem, where will you be getting the info on where to end?
Such as, does the user provide the end of the range? Is it just the last
non-empty store in column P? Once you know that it will be no prob.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Eduardo" wrote:

Hi to all, thanks to your help I am almost their on getting an EDI format
file from an excel file. What I need to do is to select a variable range i.e.
J10 to P ?? from sheet1 and copy it into cell c17 in sheet 2
thank you