copying to another workbook

[ashani]

Hi

I wonder if someone could please guide me, I'm looking to copy certain cells like a1, g3, c5 from the workbook - sheet Test to another workbook called Destination - sheet Test to first available row, from a1 to column b, g3 to column c and so on.

Please can someone guide me what syntax should I put in VBA.

I would like the Destination workbook to stay close.

Many thanks,

 

[ashani]

Any idea experts pls ?

 

[ashani]

I'm using the following code but the problem is :

1) it's not picking the first available row and keep overriding to the existing cells
2) Both workbooks have to be opened whilst I don't want to keep destination workbook open.
3) Also, don't want to describe the source workbook location, as it can be saved anywhere by the users

any help would be much appreciated.

Sub copy()

Dim x As Workbook
Dim y As Workbook

Set x = Workbooks.Open("C:\Users\Desktop\Test.xlsm")
Set y = Workbooks.Open("C:\Users\Desktop\Test MI.xlsx")

x.Sheets("Coaching").Range("W4:AB4").copy
y.Sheets("Coaching Hub New").Range("A1").PasteSpecial Paste:=xlPasteValues

End Sub