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

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,740
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Mike LH

Well-known Member
Joined
Mar 17, 2015
Messages
566
Hi,

Try this

Code:
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

Well-known Member
Joined
Feb 24, 2014
Messages
2,740
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,812
Members
416,884
Latest member
leeshjay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top