Copy Paste Macros

0

Hi,

I have created one macros of copy paste.

Is there any fine tune required to fasten the process of macros or Is i mention the unnecessary code which is not required. How to avoid the flash when the macros is running in excel.

Sub Macro()

With Workbooks("Copy paste macros")

        .Worksheets("sheet1").Columns("A:E").Copy

        .Worksheets("sheet2").Columns("A:E").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Application.CutCopyMode = False

    ActiveWorkbook.Save

 End With

    Call macro_timer

End Sub

Please guide further.

Thanks

Answer
Discuss

Answers

0
Selected Answer

Sunil

You can avoid the "flash" by turning off ScreenUpdating before you run the code (and restroring it after. This often gives a speed increase too but it will be tiny here since you are copying very little in your example.

A bigger saving will be obtained if you do not to call the sub macro_timer (which adds one second to the running time but for no purpose!).

Your code can be made shorter by referring to ThisWorkbook (rather than getting the name wrong as you did or Active Workbook, which could create problems) and by removing the options for PasteSpecial (since they are the defaults anyway).

Your code is still prone to failure if a user changes the sheet names but here's the revision (with some changes in bold). Also you really should name your macro something useful (not Macro!):

Sub Macro()
'
' Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
Application.ScreenUpdating = False

    With ThisWorkbook ' your code should have read Workbooks("Copy paste macros.xlsm")
            .Worksheets("sheet1").Columns("A:E").Copy
            .Worksheets("sheet2").Columns("A:E").PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
        ThisWorkbook.Save
    End With

Application.ScreenUpdating = True

End Sub
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login