efficient code to copy/paste
Hi Ivano,
You specifically asked for a one line
instruction and I responded accordingly.
However, without such condition, I
would have suggested:
'=========
Public Sub Tester()
Dim srcWb As Workbook
Dim destWb As Workbook
Dim srcSH As Worksheet
Dim destSH As Worksheet
Dim srcRng As Range
Dim destRng As Range
Set srcWb = Workbooks("Workbook1.xls")
Set destWb = Workbooks("Workbook2.xls")
Set srcSH = srcWb.Sheets("Benefit Analysis - Salary")
Set destSH = destWb.Sheets("Benefit Analysis - Salary")
Set srcRng = srcSH.Range("A6:F19")
Set destRng = destSH.Range("A6:F19")
'Your preceding code
srcRng.Copy Destination:=destRng
'Your subsequent code
End Sub
'=========
This may seem an unnecessarily lengthy
approach, but, in my experience, it
produces more efficient, more legible code
which has the additional advantage of being
easier to revise, reuse and maintain.
As a simple example, the assignment of a
range to an object variable enables that
variable to be used in subsequent code in
place of the full range address. This means
that any change in the range only needs to be
effected once, in the assignment statement,
rather than at each point of use.
In any event, in my opinion, legibility, clarity
and efficiency are more important objectives
than simple concision
---
Regards.
Norman
"Norman Jones" wrote in message
...
Hi Ivano,
I missed the .xls extension from the destination file.
The suggestion should, therefore, read:
Workbooks("Workbook.xls").Sheets _
("Benefit Analysis - Salary"). _
Range("A6:F19").Copy _
Destination:=Workbooks("Workbook2.xls").Sheets _
("Benefit Analysis - Salary").Range("A6")
---
Regards.
Norman
|