|
copy and paste with 2 different workbooks problem
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 |
BD3,
Use Application.ScreenUpdating = False as the first line, and Application.ScreenUpdating = True as the last. HTH, Bernie MS Excel MVP "bigdaddy3" wrote in message ... 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 |
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").Ran ge("I4:I10").Value HTH, Bernie MS Excel MVP "bigdaddy3" wrote in message ... 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 |
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").Ran ge("I4:I10").Value HTH, Bernie MS Excel MVP "bigdaddy3" wrote in message ... 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 |
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" wrote in message ... 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").Ran ge("I4:I10").Value HTH, Bernie MS Excel MVP "bigdaddy3" wrote in message ... 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 |
| All times are GMT +1. The time now is 11:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com