Macros to copy, paste, insert and loop in XL

Microsoft Excel 2003 (full)
September 16, 2010 at 13:19:12
Specs: Windows XP
Hi,

I am trying to do the following:

I have a spreadsheet with 700 rowns. I would like to write a macro to copy a row and paste it 59 times, then go to the next row and paste it 59 times...until i get to row 700, whcih is the last row. I managed to write a macro to insert 59 rows for all 700 lines but i can't get the copying and pasting part done. Can someone suggest a macro for this?

Thanks, Matt


See More: Macros to copy, paste, insert and loop in XL

Report •


#1
September 16, 2010 at 14:36:46
If you mean that whatever you have in:

Row (1) should end up in Rows (1:60)
Row (2) should end up in Rows (61:120)
Row (3) should end up in Rows (121:180), etc.

then try this:

Option Explicit
Sub Insert59()
Dim nxtRow As Long
 For nxtRow = 1 To 42000 Step 60
   Rows(nxtRow).Copy
   Rows(nxtRow + 1 & ":" & nxtRow + 59).Insert shift:=xlDown
 Next
End Sub


Report •

#2
September 17, 2010 at 06:11:30
To DerbyDad03: A thank you doesnt seem to do justice to the code you sent me! It works perfectly and does exactly what I needed it to do. I spent an hour over the phone with our Excel experts at work and they couldn't figure this out! Very appreciated!.

Report •

#3
September 17, 2010 at 09:19:06
Glad I could help.

Feel free to share my solution with your co-workers as a learning tool.


Report •

Related Solutions


Ask Question