Code to copy range in sheet 1 then paste multiple times in sheet2

[mritchey]

I will start off by apologizing that I am on my mobile and cannot attach a sample file. Hopefully my description can suffice.

I am new to VBA and am having an issue getting a bit of code to work efficiently.

I am trying to copy a range of data from sheet1 e5:e50. I would like to then paste the data range starting in e2 on sheet2 move down to the next available cell in column e, paste the range again, etc.. When the loop completes, the range should've been pasted 26 times.

One caveat on the range selected from sheet1 - the number or cells filled with values may vary. For example, there may only be 10 rows with data to copy over, other times there may be 20 or so. But data always starts in cell e5. Is there a way to select e5 then copy down xl.down?

I have some more code that inserts formulas and does some other things to create a data table but I am stuck on getting to data to copy over multiple times.

Any help would be greatly appreciated. I have been stuck on this for 3 days.

Mark

 

[Nebu]

Hi:
Use the following code.

Sub test()
Application.ScreenUpdating = False

i& = Sheet1.Cells(Rows.Count, 5).End(xlUp).Row
k& = 2

For j& = 1 To 26
    Sheet1.Range("E5:E" & i).Copy
    Sheet2.Range("E" & k).PasteSpecial
    Application.CutCopyMode = False
    k = k + 1
Next

Application.ScreenUpdating = True
End Sub

Thanks

 

[Hui]

Mark

Firstly, Welcome to the Chandoo.org forums

Sub Macro1()

  Selection.Copy

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

  For i = 1 To 25
  Selection.End(xlDown).Offset(1, 0).Select
  ActiveSheet.Paste
  Next
  Application.CutCopyMode = False

End Sub

or a more streamlined version

Sub Macro2()
  rw& = (Selection.Rows.Count) * 26 + 1
  Selection.Copy Worksheets("Sheet2").Range("E2:E" & rw)
End Sub

These both assume you have selected the cells on Worksheet1 first

 

[Hui]

Nebu

I think your code should be:

 

[mritchey]

Thank you for your assistance. I will follow up once I have the chance to run the script. I've been losing my mind trying to solve this. Still much to learn!

 

[Nebu]

@Hui Yes you are correct. I have modified the code accordingly.

Sub test()
Application.ScreenUpdating = False

i& = Sheet1.Cells(Rows.Count, 5).End(xlUp).Row
k& = 2
Sheet1.Range("E5:E" & i).Copy

For j& = 1 To 26
    Sheet2.Range("E" & k).PasteSpecial
    k = k + (i - 4)
Next

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Thanks

 

[mritchey]

I apologize for the delay but I've been on vacation. I just wanted to thank you both for your assistance. I tried out the code you helped me with this morning and it worked flawlessly.

I sincerely appreciate the help. More than that - I was able to learn what I was doing wrong!

Thank you!