CODEWITHSUNDEEP
excelmacrosvba
Neelia
Neelia

Reputation: 11

Excel Copy formulas using macros

I am trying to get Excel to automate certain tasks, eg. Copy formula in A1 down from A2 to A5000, and then to copy formula in D1, from D2 to D5000

If it's just 2 different columns, I'll just copy and paste, but it's actually for 60 columns in the same tab, and these formula columns are not next to each other.

Any suggestions to speed things up? Much appreciated!

Thanks

Upvotes: 1

Views: 88

Answers (1)

user4039065
user4039065

Reputation:

Assign the column index numbers to an array and loop through them.

Sub extendFormulas()
    Dim c As Long, fr As Long, lr As Long, arrCols As Variant

    arrCols = Array(1, 4)       'assign a collection of the column index numbers; this is A and D
    fr = 1                      'assign a starting row that already has the formula
    lr = 5000                   'assign a finishing row
    With Worksheets("Sheet1")
        For c = LBound(arrCols) To UBound(arrCols)
            .Range(.Cells(fr, arrCols(c)), .Cells(lr, arrCols(c))).Formula = _
                .Cells(fr, arrCols(c)).Formula
        Next c
    End With
End Sub

Upvotes: 3

Related Questions