How to stop the screen from 'flashing' when copying and pasting

[BaggieDan]

A bit of background:

I have created a worksheet that allows people to record the relevant details
when carrying out audits. The information that they complete is located in
various cells in the worksheet.

These audits then need to be collated and analyised on a 'master' document.

I have written a Macro that activates the audit form, selects the relevant
cell, copies the information and then activates the master document, pastes
the values of the cell in the next available column, then repeats this
process until all the relevant cells have been copied. An example of the
code is below :

Workbooks("Audit.xls").Activate
Range("C3").Select
Selection.Copy
Workbooks("Master.xls").Activate
Worksheets("VHOOC").Activate
NextColumn = Application.WorksheetFunction.CountA(Range("1:1")) + 1
Cells(1, NextColumn).Select
Selection.PasteSpecial Paste:=xlPasteValues
Workbooks("Audit.xls").Activate
Range("H5:H8").Select
Selection.Copy
Workbooks("Master.xls").Activate
Cells(2, NextColumn).Select
Selection.PasteSpecial Paste:=xlPasteValues
Workbooks("Audit.xls").Activate

etc, etc, etc

I did it like this as the cells that need copying are in multiple ranges and
I don't know how to select many ranges in one go. The above code works and
it does what I want it to. The problem I have is that because it Activates
the worksheets the screen constantly 'flashes' between the two,as it
automates the select copy paste proceedure. Is there a way to stop this?
Can I get excel to select, copy and paste the cells without the screen
changing?


Sorry this goes on a bit but I hope it gives an accurate description of what
I want to stop happening.

I have only been learning the Macro side of things for two weeks out of a
book, so please bear with me!

 

[Jim Thomlinson]

Try this

Applicaiton.Screenupdating = false '****
Workbooks("Audit.xls").Activate
Range("C3").Select
Selection.Copy
Workbooks("Master.xls").Activate
Worksheets("VHOOC").Activate
NextColumn = Application.WorksheetFunction.CountA(Range("1:1")) + 1
Cells(1, NextColumn).Select
Selection.PasteSpecial Paste:=xlPasteValues
Workbooks("Audit.xls").Activate
Range("H5:H8").Select
Selection.Copy
Workbooks("Master.xls").Activate
Cells(2, NextColumn).Select
Selection.PasteSpecial Paste:=xlPasteValues
Workbooks("Audit.xls").Activate
Applicaiton.Screenupdating = true '****

 

[Mike]

Put this in the start of your code
application.screenupdating = false

Put this in the end of your code
application.screenupdating = true

 

[Roger Govier]

Hi

Application.Screenupdating = False
Code
Application.ScreenUpdating = True

You can simplify your code and prevent all the activating and selecting etc.
by setting up what your Source and destination worksheets are, then
explicitly say Source = Destination.value as below

Dim wbS As Workbook, wbD As Workbook
Dim wSs As Worksheet, wDs As Worksheet
Dim Nextcolumn As Long

Application.ScreenUpdating = False
Set wbS = Workbooks("Audit.xls") ' Source book
Set wbD = Workbooks("Master.xls") ' destination book
Set wSs = wbS.Sheets("Sheet1") ' Change to Source Sheet name
Set wDs = wbD.Sheets("VHOOC") ' Destination Sheet

Nextcolumn = wDs.Cells(1, 1).End(xlToRight).Column + 1

wDs.Cells(1, Nextcolumn) = wSs.Range("C3").Value
wDs.Cells(2, Nextcolumn) = wSs.Range("H5:H8").Value
'etc.
'etc.
Application.ScreenUpdating = True

 

[BaggieDan]

Thank you all, as its bed time now I'll give them a go tomorrow.

Thannks again!!