CODEWITHSUNDEEP
excelvba
Maretta
Maretta

Reputation: 3

How to use the Loop with repetitive macros?

I have about 3 macros that are long and repetitive. I can't seem to figure out the loop syntax to use to make this easier. Any ideas?

Macro 1

Range("A6:L6").Copy
Range("D3:O3").PasteSpecial Paste:=xlPasteValues

Range("A10:L10").Copy
Range("D4:O4").PasteSpecial Paste:=xlPasteValues

Range("A14:L14").Copy
Range("D5:O5").PasteSpecial Paste:=xlPasteValues

Range("A18:L18").Copy
Range("D6:O6").PasteSpecial Paste:=xlPasteValues

Macro 2

Range("A7").Copy
Range("A4").PasteSpecial Paste:=xlPasteValues

Range("A11").Copy
Range("A5").PasteSpecial Paste:=xlPasteValues

Range("A15").Copy
Range("A6").PasteSpecial Paste:=xlPasteValues

Range("A19").Copy
Range("A7").PasteSpecial Paste:=xlPasteValues

Macro 3

 Range("B4").Copy
Range("B3").PasteSpecial Paste:=xlPasteValues

 Range("B8").Copy
Range("B4").PasteSpecial Paste:=xlPasteValues

 Range("B12").Copy
Range("B5").PasteSpecial Paste:=xlPasteValues

Upvotes: 0

Views: 50

Answers (2)

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

Looks like you want to understand better how loops work, so I made easy loops to understand, based on your 3 macros:

'Macro 1 could be
Dim i As Long
Dim ZZ As Long

ZZ = 3

For i = 6 To 18 Step 4
    Range("D" & ZZ & ":O" & ZZ).Value = Range("A" & i & ":L" & i).Value
    ZZ = ZZ + 1
Next i

'MAcro2 could be
Dim i As Long
Dim ZZ As Long

ZZ = 4
For i = 7 To 19 Step 4
    Range("A" & ZZ).Value = Range("A" & i).Value
    ZZ = ZZ + 1
Next i

'Macro3 could be
Dim i As Long
Dim ZZ As Long

ZZ = 3

For i = 4 To 12 Step 4
    Range("B" & ZZ & ":O" & ZZ).Value = Range("B" & i & ":L" & i).Value
    ZZ = ZZ + 1
Next i

Hope this throws some light. Anyways, you better check The For Next and For Each Loops Explained for VBA & Excel

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166241

You could do it like this:

Dim rngCopy As Range, rngPaste As Range, n as long

With ActiveSheet
    Set rngCopy = .Range("A6:L6")
    Set rngPaste = .Range("D3") 
End With

For n = 1 to 4
    rngPaste.Resize(rngCopy.Rows.Count, rngCopy.Columns.Count).Value = rngCopy.Value
    
    Set rngCopy = rngCopy.Offset(4, 0)
    Set rngPaste = rngPaste.Offset(1, 0)

Next n

Note if you just need to copy values you can assign them directly without the copy/paste steps.

Upvotes: 3

Related Questions