EXCEL running efficiency

Question

I am trying to improve the efficiency of my following code which is a pretty easy copy paste task

For i = 2 To 60
some_data = xxx
some_data_2 = xxx


Sheets("template1").Select
Sheets("template1").Copy Before:=Sheets(20)


Sheets("example").Select
Range(Cells(some_data + 1, i), Cells(some_data + some_data_2, i)).Select
Selection.Copy



Sheets("template1 (2)").Select
Range("C5").Select
ActiveSheet.Paste

I am only looping this for 60 sheets, then it took several minutes and Excel exited automatically(crahsed?). How can I improve its efficiency?

EIDT: Will it help if I try to rewrite the whole thing using python via for instance openpyxl?

Based on your provided code: see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Jul 31 '19 at 02:40
if `xxx` is static, they should be set outside the loop, not in it. Even better, if `some_data + some_data_2` is static, set that prior to the loop. Probably won't make much difference timewise, but it's definitely more efficient. — Nick.Mc, Jul 31 '19 at 02:43
if you are using `some_data + some_data_2` why not make the constant `some_data_2 = some_data + x`!? Then you can change this `some_data + some_data_2` to simply this `some_data_2` — alowflyingpig, Jul 31 '19 at 04:53

score 0 · Answer 1 · answered Jul 31 '19 at 03:28

You can Benefit from How to Avoid Using Select
You can also Make use of Application.Screenupdating = False at start of your code and True in the End. That should make it significantly faster.
Include Error handling, Crashing could be because of Error that occurs somewhere in the Loop.

Change your code without select & Activate like:

    some_data = xxx
    some_data_2 = xxx


   For i = 2 To 60

    Sheets("template1").Copy Before:=Sheets(20)

    Sheets("example").Range(Cells(some_data + 1, i), Cells(some_data + some_data_2, i)).Copy

    Sheets("template1 (2)").Range("C5").Paste

EXCEL running efficiency

1 Answers1