MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Using a macro to copy a dynamic range multiple times


Posted by Tim on March 02, 2001 2:08 PM

Help!
I've recorded a macro that copies the data in columns A-E and down to the last record (In this case row 3139). Each time the data is copied into the next available cell below the previous data, such as A3140.
The reason I wrote this macro is because this process must be repeated 8 times. However, my problem is that
this macro only works on a sheet with the exact number of rows as the original. Is there a way to change the code to allow for a dynamic range to copy?

Sub Copy8()
Range("A2:E2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.End(xlDown).Select
Range("A3139").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
Range("A6276").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
Range("A9413").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
Range("A12550").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
Range("A15687").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
Range("A18824").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
Range("A21961").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
Range("A25098").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.End(xlUp).Select
End Sub


If anyone can help, it would be greatly appreciated!

Tim


Posted by David Hawley on March 02, 2001 5:37 PM


Hi Tim

Here is a much quicker way using a Loop.


Sub Copy8()
Dim i As Integer
Range("A2:E2", Range("A2:E2").End(xlDown)).Copy
For i = 1 To 8
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Next i
Application.CutCopyMode = False
End Sub


Hope this helps


OzGrid Business Applications

Posted by san on March 03, 2001 3:17 AM

hi,

is this a similar solution to my problem - see "How do I choose different columns of data to run same macro?" 03/02/01

Could I run a loop that each time selects a new column of data and plots a chart from it? how do you do it?

sean