Loop copying in the column rows

Question

Good morning,

I would like to ask you how to loop copy a bulk data values in rows.

In my case there is a tens tables, where I have to input data. According to Excel formulas I can copy it using Paste Formulas option.

However in VBA, when I use a code below it seems to be hefty:

Sub sum_month()
Sheets("13").Activate
Range("EG822").Formula = "=SUM(EG12+EG282+EG552)"            '-4
Range("EG822").Copy
Range("EG822:FT846").PasteSpecial xlPasteFormulas
Range("EG822:FT846").Copy
Range("FY822:HL846").PasteSpecial xlPasteFormulas
Range("HR822:JE846").PasteSpecial xlPasteFormulas

Range("EG852").Formula = "=SUM(EG42+EG312+EG582)"            '-3
Range("EG852").Copy
Range("EG852:FT876").PasteSpecial xlPasteFormulas
Range("EG852:FT876").Copy
Range("FY852:HL876").PasteSpecial xlPasteFormulas
Range("HR852:JE876").PasteSpecial xlPasteFormulas

Range("EG882").Formula = "=SUM(EG72+EG342+EG612)"            '-2
Range("EG882").Copy
Range("EG882:FT906").PasteSpecial xlPasteFormulas
Range("EG882:FT906").Copy
Range("FY882:HL906").PasteSpecial xlPasteFormulas
Range("HR882:JE906").PasteSpecial xlPasteFormulas
End Sub

Basically I have to make to copy in step 30 within the same row (the issue refers to many columns as per code above).

Is anyone clued up how to deal with it?

Thanks,

I'm confused ! What's the problem here? – Imran Malek Oct 18 '18 at 08:05 — Imran Malek, Oct 18 '18 at 08:05

score 0 · Answer 1 · answered Oct 18 '18 at 08:16

You can use a For i loop with Step 30 and then .Offset from your first range i rows.

Note that the sum formula needs to be adjusted too.

Sub sum_month()
    Dim i As Long
    For i = 0 To 60 Step 30
        With Sheets("13")
            .Range("EG822").Offset(RowOffset:=i).Formula = "=SUM(EG" & 12 + i & "+EG" & 282 + i & "+EG" & 552 + i & ")"    '-4
            .Range("EG822").Offset(RowOffset:=i).Copy
            .Range("EG822:FT846").Offset(RowOffset:=i).PasteSpecial xlPasteFormulas
            .Range("EG822:FT846").Offset(RowOffset:=i).Copy
            .Range("FY822:HL846").Offset(RowOffset:=i).PasteSpecial xlPasteFormulas
            .Range("HR822:JE846").Offset(RowOffset:=i).PasteSpecial xlPasteFormulas
        End With
    Next i
End Sub

I recommend never to use .Activate or .Select. See How to avoid using Select in Excel VBA.

I have modified it. Your code works, thank you mate. – Geographos Oct 18 '18 at 13:25 — Geographos, Oct 18 '18 at 13:25
Please mark it as solution if it solved your question. – Pᴇʜ Oct 18 '18 at 13:25 — Pᴇʜ, Oct 18 '18 at 13:25

score 0 · Answer 2 · answered Oct 18 '18 at 13:24

This formula works:

Sub sum_1to10_fillUK()
Dim i As Long
For i = 0 To 474 Step 52  'total amount of rows = 472, that includes 9 tables (each with 52 rows) + 3 rows free spaces between them
With Sheets("14")
.Range("FP12").Offset(RowOffset:=i).Copy     'The SUM for FP12 has been calculated in another loop, so I am only copying the formulas from here.
.Range("FP12:HN58, HT12:JR58, JX12:LV58").Offset(RowOffset:=i).PasteSpecial xlPasteFormulas               ' where FP-HN is 1st table, HT-JR - 2nd one and JX-LV - the last one from the right (see the picture)
End With
Next i
End Sub

Loop copying in the column rows

2 Answers2