Hi,

I'm trying to write a code to copy some formulae from a spreadsheet named "ETSFALL" from Row 2 of Column T to AG to the rest of the rows under the same column. The problem i have is that the no. of rows is dynamic (In today's data, there's 26735 rows).

I have a code in blue below that is able to copy and paste the formula to the correct no. of rows but it is taking far too much time (> 10 mins). Does anyone know of a better way of doing this??

Please advise.
Thanks


Dim Range1 As String

Worksheets("ETSFALL").Select
ActiveCell.CurrentRegion.Select
Range1 = Selection.Rows.Count

Worksheets("ETSFALL").Cells(2, 1).Activate
Worksheets("ETSFALL").Cells(2, 1).Select
ActiveCell.CurrentRegion.Select
Count = Selection.Rows.Count

For i = 1 To Count
    Worksheets("ETSFALL").Range("T2:AG2").Copy
    Worksheets("ETSFALL").Cells(1 + i, 20).PasteSpecial
Next i

Hi

Try to set Calculation to manual while copying the formulas

Application.Calculation = xlCalculationManual
'Do some copies here
Application.Calculation = xlCalculationAutomatic

-vemaju

I managed to solve the problem. The code is shown below:

Dim Range1 As String

Worksheets("ETSFALL").Select
Worksheets("ETSFALL").Range("T2:AG2").Copy
ActiveCell.CurrentRegion.Select
Range1 = Selection.Rows.Count
Worksheets("ETSFALL").Cells(Range1, 20).Select
Worksheets("ETSFALL").Range(Selection, Selection.End(xlUp)).Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

;p