efficient code to copy/paste

[Ivano]

Hi,
I need to copy and paste various cells within various tabs for various
worksheets to other worksheets. So, through the Record Macro feature it came
up with a bunch of code that records every click and step but I want to make
it more efficient. This is part of the code:

Windows("Workbook1.xls").Activate
Sheets("Benefit Analysis - Salary").Select
Range("A6:F19").Select
Selection.Copy
Windows("Workbook2.xls").Activate
Sheets("Benefit Analysis - Salary").Select
Range("A6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Is there way in a single comand line I can tell it to:
copy range "A6:F19" from "Benefit Analysis - Salary" from "workbook1"
paste values to range "A6" of "Benefit Analysis - Salary" from "workbook2"

Thanks
Ivano

 

[Don Guillett]

This assumes your macro fired from the destination workbook.

Sub copyvaluesfrom()
Workbooks("sourc.xls").Sheets("sheet1").Range("a2:a22").Copy
Range("a6").PasteSpecial Paste:=xlPasteValues
End Sub

 

[Norman Jones]

Hi Ivano,

Try:

Workbooks("Workbook.xls").Sheets _
("Benefit Analysis - Salary"). _
Range("A6:F19").Copy _
Destination:=Workbooks("Workbook2").Sheets _
("Benefit Analysis - Salary").Range("A6")

 

[Norman Jones]

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")

 

[Ivano]

Thanks Don, it works.

This assumes your macro fired from the destination workbook.

Sub copyvaluesfrom()
Workbooks("sourc.xls").Sheets("sheet1").Range("a2:a22").Copy
Range("a6").PasteSpecial Paste:=xlPasteValues
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Norman Jones]

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

 

[Ivano]

HI Norm,
you are right, your one liner does conform to my requirement.
The structure of your second suggested code does make things more efficient
in the end for me since I have many spread sheets to do this with. However,
not being very experienced with VBA I will need to take your code, go over it
and tweek it to fit my situation.
Thanks very much for coming back with the suggestion... it will make my life
easier in the end.

Ivano

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

 

[Ivano]

I ran into a snag. A1 to D1 are merged and A2 to D2 are also merged

from workbook1 - ".Range("A12").copy" and
to workbook2 - ".Range("A56").PasteSpecial Paste:=xlPasteValues

I get runtime error: PasteSpecial method of Range class failed

But if I change the destination of workbook2 to paste to A12 then it
works... it's like I can't past special to any other destination other then
where it got copied from?

any ideas?