CODEWITHSUNDEEP
excelvba
Nishith
Nishith

Reputation: 49

Copy Formula or Fill down

I'm using vba to copy formula from multiple cells and paste to a large number of cells. Here's my code -

Sub FillFormulae()
Application.ScreenUpdating = False

Dim WS As Worksheet: WS = Sheets("Main")
WS.Range("E15:P15").Copy
WS.Range("E16:P" & MainDataLastCell).PasteSpecial xlPasteFormulas
WS.Range("S15:EA15").Copy
WS.Range("S16:EA" & MainDataLastCell).PasteSpecial xlPasteFormulas

End Sub

MainDataLastCell is a global variable which has a value for 312896 for the current data. This can be more or less depending on the data.

The problem is this code is taking more than 4 hours to complete. Do you guys think if I use "Filldown" instead of copy formula it will take less time?

Upvotes: 2

Views: 354

Answers (2)

brettdj
brettdj

Reputation: 55682

Suggest that you try

  1. turning off calculation
  2. turning off screenupdating

The code below also fixes the compile error as WS = Sheets("Main") won't work

Const MainDataLastCell = 312896

Sub FillFormulae()
Application.ScreenUpdating = False

Dim WS As Worksheet
Dim lngCalc As Long

Set WS = Sheets("Main")

With Application
    lngCalc = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With

With WS
    .Range("E16:P" & MainDataLastCell).FormulaR1C1 = .Range("E15:P15").FormulaR1C1
    .Range("S16:EA" & MainDataLastCell).FormulaR1C1 = .Range("S15:EA16").FormulaR1C1
End With


With Application
    .Calculation = lngCalc
    .ScreenUpdating = True
End With

End Sub

Upvotes: 2

Thomas Bescherer
Thomas Bescherer

Reputation: 128

How about the below? With a simple formula this is only taking about a minute to run on my machine.

Sub fillformulae()
Application.ScreenUpdating = False

Dim icol As Integer
Dim irow As Integer
Dim WS As Worksheet: WS = Sheets("Main")

For icol = 5 To 16
    WS.Range(Cells(16, icol), Cells(MainDataLastCell, icol)).Formula = WS.Cells(15, icol).Formula
Next

For icol = 19 To 131
    WS.Range(Cells(16, icol), Cells(MainDataLastCell, icol)).Formula = WS.Cells(15, icol).Formula
Next
Application.ScreenUpdating = True
End Sub

Upvotes: 1

Related Questions