0

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.

Improve this question
2
  • Yes, there is a way. Read this Q/A please. Commented Jan 26, 2014 at 19:55
  • Post your current code. Commented Jan 26, 2014 at 20:11

2 Answers 2

Reset to default
1

Two things to improve:

  1. 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.

  2. 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...

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

You can try:

Application.ScreenUpdating = False

use this code at the beginning of your vb project.

Improve this answer

1 Comment

Thanks everyone. I used Peter Albert's and it was fine

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.