Select a variable range

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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" & lastRow).Copy

Sheets("Sheet2").Select
Sheets("Sheet2").Range("C17").Select
ActiveSheet.Paste

 

[Guest]

Dim rng as Range
With Activesheet
set rng = .Range(.Range("J10"),.Cells(rows.count,"P").End(xlup))
End With
rng.copy Worksheets("Sheet2").Range("C17")

 

[Guest]

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

 

[Guest]

Thank you John, I figure out how to paste value. Your help was great
Eduardo

 

[Guest]

Change that last Activesheet.Paste to
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False