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