How to copy selection.address cell values to another workbook.?

[miqbal]

hi,
I am new to VBA and tryin to reduce some workload to make a small VBA program to copy range randomly with mouse and paste it to destination file.
source file is "Mytest.xlsm" and destination file is "BatchEntry.xlsx"

I can easily copy range by below code and successfully paste It to other sheet but when I tried to paste it to another workbook it gives me error.

so below is the code I ran it in source file which is "Mytest.xlsm"

Sub copytoBatchEntry()

Set areatocopy = Sheet1.Range(Selection.Address)
areatocopy.Copy

'sheet2.range("a2").PasteSpecial ---- it works without any error for current workbook's worksheet.

Workbooks.Open ("U:\Batch Entry.xlsx")
Windows("Batch Entry.xlsx").Activate

Windows("Batch Entry.xlsx").Sheets("sheet1").Range("a2").PasteSpecial
End Sub


the above code gives error
"Run Time Error: 438 object doesn't support this property or method."

Please if someone can help to point out what I am doing wrong here.

 

[pmich]

Welcome.
But if you want the format in the selected cell to be copied (for example background colour or Font Style etc.,) then you may have to edit this code...

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

as this ...

 Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False