Quick Copy/Paste Macro question

[soudoughnibblers]

Hi, I've been struggling (since I don't know much VBA) on how to write a simple Excel 2010 macro. Basically, I want to copy five specific blocks from every sixth row, and then paste those five blocks on the rows between every sixth row. These sets of five rows are currently empty. Here's the script I have that doesn't work:

Sub spacing()

Dim i As Integer

For i = 2 To 3956 Step 6

Range("Ii:Li").Copy
Range("Ei+1:Hi+1").Paste

Range("Mi:Pi").Copy
Range("Ei+2:Hi+2").Paste

Range("Qi:Ti").Copy
Range("Ei+3:Hi+3").Paste

Range("Ui:Xi").Copy
Range("Ei+4:Hi+4").Paste

Range("Yi:ABi").Copy
Range("Ei+5:Hi+5").Paste

Next i

End Sub

Thanks in advance for your help!

 

[shg]

Welcome to the board.

Try this:

Sub spacing()
    Dim i         As Long
 
    For i = 2 To 3956 Step 6
        Rows(i).Range("I1:L1").Copy Rows(i + 1).Range("E1")
        Rows(i).Range("M1:P1").Copy Rows(i + 2).Range("E1")
        Rows(i).Range("Q1:T1").Copy Rows(i + 3).Range("E1")
        Rows(i).Range("U1:X1").Copy Rows(i + 4).Range("E1")
        Rows(i).Range("Y1:AB1").Copy Rows(i + 5).Range("E1")
    Next i
End Sub

 

[tweedle]

Welcome. You need to isolate you variable i for the Ranges to work.

Dim i As Integer
For i = 2 To 3956 Step 6
Range("I" & i & ":L" & i).Copy Range("E" & i + 1 & ":H" & i + 1)
Range("M" & i & ":P" & i).Copy Range("E" & i + 2 & ":H" & i + 2)
Range("Q" & i & ":T" & i).Copy Range("E" & i + 3 & ":H" & i + 3)
Range("U" & i & ":X" & i).Copy Range("E" & i + 4 & ":H" & i + 4)
Range("Y" & i & ":AB" & i).Copy Range("E" & i + 5 & ":H" & i + 5)
Next i
End Sub

 

[soudoughnibblers]

Welcome. You need to isolate you variable i for the Ranges to work.

Dim i As Integer
For i = 2 To 3956 Step 6
Range("I" & i & ":L" & i).Copy Range("E" & i + 1 & ":H" & i + 1)
Range("M" & i & ":P" & i).Copy Range("E" & i + 2 & ":H" & i + 2)
Range("Q" & i & ":T" & i).Copy Range("E" & i + 3 & ":H" & i + 3)
Range("U" & i & ":X" & i).Copy Range("E" & i + 4 & ":H" & i + 4)
Range("Y" & i & ":AB" & i).Copy Range("E" & i + 5 & ":H" & i + 5)
Next i
End Sub

Thank you, Tweedle. This worked perfectly!

 

[tweedle]

Hi shg;

Does this work because you're limiting the scope of Range to just a single row?

Rows(i).Range("I1:L1").

 

[shg]

Yes, it's interpreted in the context of the top left cell of the qualifying range.

 

[tweedle]

Thank You!; I learned something new today - mission accomplished.