Excel 2010 work with multiple named ranges

Question

My question is: Im working with named ranges in a large workbook. I need to copy data say from some named range in some worksheet x and paste it on another named range say wksheet y before i can sort it and display it on yet another range in wksheet z.

I have achieved all this through vba except for screen flickers alot when i execute the code.

Im using the statement application.goto reference:="" to select the ranges and this activates these sheets hence the flickering back n forth. which dosent look appealing.

Is there a way i could work with the sheets without activating them.

Thank you.

Yes, there is a way. Read [this Q/A](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) please. — Dmitry Pavliv, Jan 26 '14 at 19:55

score 1 · Accepted Answer · answered Jan 26 '14 at 21:06

Two things to improve:

Start your code with:

Application.ScreenUpdating = False

This will prevent the flickering. Make sure to reactivate it at the end with Application.ScreenUpdating = True. Else Excel will behave strangely, not updating the screen correctly after your macro is finished.

Instead of using

   Application.Goto Reference="YourRangeName1"
   Selection.Copy
   Application.Goto Reference="YourRangeName2"
   Selection.Paste

use

Range("YourRangeName1").Copy Target=Range("YourRangeName2")

This will also speed up your execution time...

score 0 · Answer 2 · answered Jan 26 '14 at 20:22

0

You can try:

Application.ScreenUpdating = False

use this code at the beginning of your vb project.

answered Jan 26 '14 at 20:22

user3238372

1

Thanks everyone. I used Peter Albert's and it was fine – gondwe Aug 15 '14 at 08:05

Excel 2010 work with multiple named ranges

2 Answers2