VBA To Enter Formulas Into Range & Copy

    I'm trying to shed some 'fat' off two macros I have, trying to make them run as quickly as possible. I wonder if my macros below can be further optimized:


    Code
    i = 9
Do Until Worksheets("RST").Cells(i, 2) = ""
Range("H9").FormulaR1C1 = "=IF(AND(RC[-2]<>"""",RC[1]=""""),WORKDAY(RC[-2],5,RC[-2]),"""")"
Range("H9").Copy Destination:=Worksheets("RST").Cells(i, 8)
i = i + 1
Loop


    and


    Code
    Sheets("RST Pivot").Select
Range("H6:I1000").Copy
Sheets("RST").Select
Range("G9").PasteSpecial Paste:=xlPasteValues

    Re: Optimizing Copying Method


    You don't need to use the select statement.

    Code
    Application.ScreenUpdating = False
    Sheet1.Range("H6:I1000").Copy 'sheete code name used - alter to suit
    Sheet2.Range("G9").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Application.ScreenUpdating = True

    Re: Optimizing Copying Method


    Code
    Sub blah()
Sheets("RST Pivot").Range("H6:I1000").Value = Sheets("RST").Range("G9:H1003").Value
End Sub

    or if you won't know the size of the range

    Code
    Sub blah2()
Set SourceRng = Sheets("RST").Range("G9:H1003")
Sheets("RST Pivot").Range("H6").Resize(SourceRng.Rows.Count, SourceRng.Columns.Count).Value = SourceRng.Value
End Sub


    For the first macro, you don't need to enter formulae one cell at a time; the likes of

    Code
    Range("H9:H1003").FormulaR1C1 = "=IF(AND(RC[-2]<>"""",RC[1]=""""),WORKDAY(RC[-2],5,RC[-2]),"""")"

    will do them all in one hit

    Re: VBA To Enter Formulas Into Range &amp; Copy


    Thanks for the wonderful suggestions, they work beautifully! I love this community because it's easy to learn new things quickly. I find that no matter how many books I read about VBA, there's rarely an example mentioned to suit my needs. That's why this community works much better than any books.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login