'flashing'

  • Thread starter Thread starter Dewi...
  • Start date Start date
D

Dewi...

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







- Show quoted text -
Click to expand...

Many thanks, did what i wanted.
 
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
 
Back
Top