How can I make this copy paste function faster???

**just_helping** · 05-02-2012, 11:04 AM

Hi,

I am trying to make this code run quicker but am not very familiar with VBA. Any help is much appreciated. Thank you.

Function midterm(wksName As String, row_Src As Long, column_Dst As String, srch_Term As String)
    Application.ScreenUpdating = False
    
    Worksheets(wksName).Activate
    Range(Cells(row_Src, 1), Cells(row_Src, 10)).Select
    Selection.Copy
    
    Worksheets("midterm").Activate
    Cells(2, 1).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
End Function

The code is taking a range from one worksheet and moving that range to another location but when I run this code over several thousand lines it takes forever. Is there any way to make it quicker? Thank you again for your help.

**AlvaroSiza** · 05-02-2012, 11:49 AM

Is there a reason it is a function rather than a sub?

I would propose (and the following assumes that your current calculation mode is set to automatic (Excel Options >> Formulas >> Calculation Options >> Advanced = True):

Function midterm(wksName As String, row_Src As Long, column_Dst As String, srch_Term As String)

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual    

       Worksheets(wksName).Range(Cells(row_Src, 1), Cells(row_Src, 10).Copy
       Worksheets("midterm").Cells(2, 1).PasteSpecial Paste:=xlValues, _
                  Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic    

End Function

How can I make this copy paste function faster???

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How can I make this copy paste function faster???

Re: How can I make this copy paste function faster???

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions