Speed up

[PIsabel]

Hello.
How can I speed up this code?
My file is from line 2 to line 30000
my macro inserts the formulas in line 2 and then copies and pastes from line 3 to 30000
"Range("B2:C2").Select" and Range("F2:AI2").Select have long formulas which makes the file very heavy. The macro itself is struggling
Most of the time I only have half a dozen lines to use.
What I need is a code that only runs up to the last cell in column E

Thanks

    'copy and paste formulas
    Range("B2:C2").Select
    Selection.Copy
    Range("B3:C30000").Select
    ActiveSheet.Paste

    'copy and paste formulas
    Range("F2:AI2").Select
    Selection.Copy
    Range("F3:AI30000").Select
    ActiveSheet.Paste

 

[Special-K99]

Am no VBA expert but I often see people say remove the Select for a start
If you're just copying formulas, maybe simply

Range("B3:C30000") = Range("B2:C2").Formula
Range("F3:AI30000") = Range("F2:AI2").Formula

 

[Alex Blakenburg]

Try:

Sub CopyFormulaDown()

    Dim ws As Worksheet
    Dim LastRow As Long
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    Set ws = ActiveSheet
    
    With ws
        LastRow = .Range("E" & Rows.Count).End(xlUp).Row
        .Range("B2:C" & LastRow).Formula = .Range("B2:C2").Formula
        .Range("F2:AI" & LastRow).Formula = .Range("F2:AI2").Formula
    End With
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

 

[PIsabel]

Thanks Alex Blakenburg

The code works perfectly.
And if I want to copy data from one sheet to another, is there a way to change this code to work from the "AAA" sheet to the "BBB" sheet?

 

[Alex Blakenburg]

Using the assignment approach (.Formula = .Formula) on multiple columns caused some relative reference issues when I used it to copy to mulitple rows at a different location, so I have gone back to using copy-paste. I think most of the speed gains were in "turning off" screenupdating and calculations but let me know if this is too slow.

Sub CopyFormulaDown_DifferentSheet_Copy()

    Dim wsSrc As Worksheet, wsDest As Worksheet
    Dim LastRow As Long
 
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
 
    Set wsSrc = Worksheets("Sheet1")                ' <--- Change Sheet1 to sheet containing formula
    Set wsDest = Worksheets("Sheet2")               ' <--- Change Sheet2 to sheet to copy to
 
    With wsDest
        LastRow = .Range("E" & Rows.Count).End(xlUp).Row
        wsSrc.Range("B2:C2").Copy Destination:=.Range("B2:C" & LastRow)
        wsSrc.Range("F2:AI2").Copy Destination:=.Range("F2:AI" & LastRow)
    End With
 
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

If it is too slow you could try replacing the With wsDest section with this:

    With wsDest
        LastRow = .Range("E" & Rows.Count).End(xlUp).Row
        .Range("B2:C2").Formula = wsSrc.Range("B2:C2").Formula
        .Range("B2:C" & LastRow).FillDown
        .Range("F2:AI2").Formula = wsSrc.Range("F2:AI2").Formula
        .Range("F2:AI" & LastRow).FillDown
    End With