In fact, you can dramatically improve macro performance by not using the Select method.After recording your macros, make it a habit to alter the generated code to remove the Select methods.The Excel status bar, which appears at the bottom of the Excel window, normally displays the progress of certain actions in Excel. Display Page Breaks = True End Sub If your macro manipulates pivot tables that contain large data sources, you may experience poor performance when doing things like dynamically adding or moving pivot fields.If your macro is working with lots of data, the status bar will take up some resources. You can improve the performance of your macro by suspending the recalculation of the pivot table until all pivot field changes have been made. Manual Update property to True to defer recalculation, run your macro code, and then set the Pivot Table.Set the Enable Events property to False before running your macro. If you want to continue to show page breaks after your macro runs, set the Display Page Breaks sheet property back to True. This alternate code uses the Destination argument to bypass the clipboard and copy the contents of cell A1 directly to cell B1.After your macro code is finished running, you can set the Enable Events property back to True. If you need to copy only values (not formatting or formulas), you can improve performance even more by avoiding the Copy method all together.By Michael Alexander As your Excel macros become increasingly robust and complex, you may find that they lose performance.
For instance, the following simple code forces VBA to continuously return to Sheets(“Sheet1”).
When a workbook is in manual calculation mode, the workbook will not recalculate until you explicitly trigger a calculation by pressing the F9 key.
Place Excel into manual calculation mode, run your code, and then switch back to automatic calculation mode.
This can mean that what should take a matter of 1 to 3 seconds, often takes a lot longer.
Free Excel Help One drawback with recorded macros in Excel is that the code produced is often very inefficient.