'flashing'

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dewi... - 10 Jun 2007 04:55 GMT

Hello

I have some macros (sample below) I have a button to run.

Since it copy's each cell from one worksheet to the other, about (10
items) the sheets flick back and forth as the macro runs, so looks
like a flicking screen, could cause problems etc, how can I make it so
there is not back and forth with each line.

Hope I have explained it reasonably ok...

Thanks
-------------------------------
Range("B4").Select
Selection.Copy
Sheets("Invoice List").Select
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Invoice").Select
Range("C6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Invoice List").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Invoice").Select
Range("G6").Select
etc

Reply to this Message

JE McGimpsey - 10 Jun 2007 05:10 GMT

One way:

Dim wsDest As Worksheet
Set wsDest = Worksheets("Invoice List")
With Worksheets("Invoice")
wsDest.Range("B4").Value = .Range("B4").Value
wsDest.Range("A4").Value = .Range("C6").Value
wsDest.Range("X1").Value = .Range("G6").Value
'etc...
End With

There will be no flicker, because there's no selections going on.

In general there's almost never a need to select a worksheet, range,
etc. Working directly with the objects is faster, yields smaller code,
and IMO, is easier to maintain.

> Hello
>
[quoted text clipped - 28 lines]
> Range("G6").Select
> etc

Reply to this Message

Dewi... - 10 Jun 2007 06:06 GMT

> One way:
>
[quoted text clipped - 49 lines]
>
> - Show quoted text -

Many thanks, did what i wanted.

Reply to this Message

Earl Kiosterud - 10 Jun 2007 16:57 GMT

Dewi,

JE's method is the preferred way (no need to copy, switch locations, paste, etc.). I'll add
this: You can prevent screen activity while your macro is changing stuff (although it often
looks cooler to actually see it happening) with this:

Application.ScreenUpdating = False
.
.
Code that does stuff
.
.
Application.Screenupdating = True

This will improve execution speed in many cases too.

Signature

Earl Kiosterud
www.smokeylake.com

Note: Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------

> Hello
>
[quoted text clipped - 28 lines]
> Range("G6").Select
> etc

Reply to this Message

Rate this thread: