copy and paste with 2 different workbooks problem

[bigdaddy3]

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

[Bernie Deitrick]

BD3,

Use

Application.ScreenUpdating = False

as the first line, and

Application.ScreenUpdating = True

as the last.

HTH,
Bernie
MS Excel MVP


"bigdaddy3" <[email protected]> wrote in message
news:[email protected]...
> 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

[Bernie Deitrick]

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


"bigdaddy3" <[email protected]> wrote in message
news:[email protected]...
> 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

[bigdaddy3]

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


"Bernie Deitrick" wrote:

> 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
>
>
> "bigdaddy3" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[Bernie Deitrick]

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


"bigdaddy3" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
> "Bernie Deitrick" wrote:
>
>> 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
>>
>>
>> "bigdaddy3" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>>
>>
>>