Need a macro to copy entire rows but need to list these rows in a cell?

[tonywatsonhelp]

Hi Everyone,
need a macro that will copy a number of rows from the active sheet
for example it might be rows 15to29 as an example,
the thing is this will change each time i run the macro and its impossible to identify which rows i want copied (they will all be bunched together so 15 to 50 , 25 to 60 etc.)

So what i was thinking is if i put the Start Row and End Row in cells AD1 and AD2 then maybe this could be referenced buy the macro so instead of it going copy rows 15to30, it would go copy rows (start number in active sheet AD1, End in Active sheet AD2)
then i just need it pasted into sheet1 A1 as this will always be new data!

i'm stuck as i don't know how to get a vba to reference the cell number as the row numbers? i can do the rest (i think lol )

please can someone tell me how i would do this?
thanks
Tony

 

[Mike LH]

Hi,

Try this

Sub copy_Me()
Dim First As Long, Last As Long
First = Range("AD1").Value
Last = Range("AD2").Value
Rows(First).Resize((Last - First) + 1).Copy Sheet1.Range("A1")
End Sub

 

[tonywatsonhelp]

Mike LH to the rescue again!
thank you very much,
that is exactly what i needed andso handy to have, but brilliant i just tested and as always work first time no editing needed so thank you so much
brilliant


Tony