copy and paste with 2 different workbooks problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, can anyone simplyfy this code as it is used in the workbook open event
but it blinks 4 times because the following code although it does the job i
think i ask for to many activate on the payslip side as that is the book that
when opens seems to perorm 4 operation, hense the movement, any ideas please
Workbooks("Staff Details").Activate
Range("I4:I10").Select
Selection.Copy
Worksheets("Payslip").Activate
Range("B2:B8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Workbooks("Staff Details").Activate
Range("C4").Select
Selection.Copy
Worksheets("Payslip").Activate
Range("C11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Workbooks("Staff Details").Activate
Range("C9").Select
Selection.Copy
Worksheets("Payslip").Activate
Range("K11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Workbooks("Staff Details").Activate
Range("G9").Select
Selection.Copy
Worksheets("Payslip").Activate
Range("K12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
 
BD3,

Use

Application.ScreenUpdating = False

as the first line, and

Application.ScreenUpdating = True

as the last.

HTH,
Bernie
MS Excel MVP
 
BD3,

I should have added that there is rarely any reason to select: you left some detail out of your code
(like workbook and worksheet names) but this is the equivalent of your
select/copy/selecte/pastespecial values:

Workbooks("Book3.xls").Worksheets("Payslip").Range("B2:B8").Value = _
Workbooks("Book2.xls").Worksheets("DebitSlip").Range("I4:I10").Value

HTH,
Bernie
MS Excel MVP
 
Hi Bernie,thanks for your help with that but with my original code and your
screen updating suggestion it works perfect so would i benefit at all using
the new part you suggest.
 
BD3,

The new code would be faster, but unless you are building a big application, that is usually not a
concern.

HTH,
Bernie
MS Excel MVP
 
Back
Top