Hi all,
I'm having trouble to copy paste cells through macro.
I'm coping/pasting both cells's formulas and format. The number of cells is something like 120 (a rectangle of 2 rows and 60 columns). The formulas are for some cells excel functions and for others macro functions.
When I copy/paste these excel through the GUI (ctrl+c/ctrl+v or copy/paste) it is really fast, like instantaneus, but if I do it through macro Sub is really slow (it takes something like between 30/60 seconds to do it). If I use the debug (F8 command) I can see that after the copy/paste action, it starts running the macro functions i wrote and that are called from the cells. It seems like this is what takes times.
I tried adding Application.Calculation = xlcalculationmanual and Application.Calculation = xlcalculationautomatic at the beginning and the end of the Sub respectively. But it doesn't change a lot since it recalculates everything at the end. I'm already using Application.ScreenUpdating = False.
What I don't understand is why it is really fast if I copy/paste through GUI and why it is so slow through macro.
I'm using excel 2010.
I also tested the worksheet on excel 2013 and it doesn't seem to have this problem even though it recalculates everything anyway.
Thanks
I'm having trouble to copy paste cells through macro.
I'm coping/pasting both cells's formulas and format. The number of cells is something like 120 (a rectangle of 2 rows and 60 columns). The formulas are for some cells excel functions and for others macro functions.
When I copy/paste these excel through the GUI (ctrl+c/ctrl+v or copy/paste) it is really fast, like instantaneus, but if I do it through macro Sub is really slow (it takes something like between 30/60 seconds to do it). If I use the debug (F8 command) I can see that after the copy/paste action, it starts running the macro functions i wrote and that are called from the cells. It seems like this is what takes times.
I tried adding Application.Calculation = xlcalculationmanual and Application.Calculation = xlcalculationautomatic at the beginning and the end of the Sub respectively. But it doesn't change a lot since it recalculates everything at the end. I'm already using Application.ScreenUpdating = False.
What I don't understand is why it is really fast if I copy/paste through GUI and why it is so slow through macro.
I'm using excel 2010.
I also tested the worksheet on excel 2013 and it doesn't seem to have this problem even though it recalculates everything anyway.
Thanks