Trying to avoid Select and activate..

[HankJ]

I've read that some suggest it is good practice to avoid using select, so I thought I would give it a go, having tied myself in knots trying to flip from sheet to sheet and file to file.

I've written the following code, which does what it is intended to, copy a range from one file to another file.

Sub Transfer()
Dim wb1 As Workbook
Set wb1 = Application.Workbooks("Arrays are us.xls")
Dim mg1 As Range
Set mg1 = wb1.Sheets("Source").Range(Cells(1, 1), Cells(3, 1))

Dim wb2 As Workbook
Set wb2 = Application.Workbooks("Home for data.xls")
Dim ws2 As Worksheet
Set ws2 = wb2.Sheets("Destination")
Dim Rng2 As Range
Set Rng2 = Cells(1, 3)

With mg1.Copy
   ws2.Paste
End With

End Sub

My problem is trying to specify where I want the range to be pasted to. If I try and insert something like ws2.cells(3,3).paste it all falls apart.

Not quite getting it.

 

[mole999]

something like this is the aim

Sheet1.Range("A1:A200").Copy Destination:=Sheet2.Range("B1")

wb1.Sheets("Source").Range(Cells(1, 1), Cells(3, 1)).copy Destination:=wb2.Sheets("Destination").range(Cells(1, 3))
)

 

[mumps]

Make sure that both workbooks are open and try:

Sub Transfer()
    Workbooks("Arrays are us.xls").Sheets("Source").Range(Cells(1, 1), Cells(3, 1)).Copy Workbooks("Home for data.xls").Sheets("Destination").Cells(1, 3)
End Sub

 

[HankJ]

something like this is the aim

Sheet1.Range("A1:A200").Copy Destination:=Sheet2.Range("B1")

wb1.Sheets("Source").Range(Cells(1, 1), Cells(3, 1)).copy Destination:=wb2.Sheets("Destination").range(Cells(1, 3))
)

Thank you for this.

I got a application defined or object defined error when I inserted this line.

I was really hoping to understand why my original code didn't work when I tried to define a specific location in the destination sheet.

Hankj

 

[HankJ]

Make sure that both workbooks are open and try:

Sub Transfer()
    Workbooks("Arrays are us.xls").Sheets("Source").Range(Cells(1, 1), Cells(3, 1)).Copy Workbooks("Home for data.xls").Sheets("Destination").Cells(1, 3)
End Sub

I did and this does the job but I was hoping to understand why my original code could not be expanded to include a specific location in the destination sheet.

Maybe I am over thinking this.

Many thanks for your time.

Hankj

 

[steve the fish]

You could try:

ws2.cells(3,3).pastespecial xlpastevalues

 

[HankJ]

You could try:

ws2.cells(3,3).pastespecial xlpastevalues

Hi Steve

That worked a treat.

Many thanks

Hankj