Hi, I have recorded a macro which simply copies a value from cell A1 in an opened workbook(Source.xlsm) to cell A1 in another opened
workbook(Destination.xlsx) listed below which works perfectly.....
Private Sub CommandButton2_Click()
Range("A1:a3").Select ' macro started from the 1st workbook.
Selection.Copy
Windows("Destination.xlsx").Activate
ActiveSheet.Paste
Windows("Source.xlsm").Activate
Selection.Clear 'finally clears the selection from the Source.xlsm after copying.
End sub
What puzzles me is why the following code below is unable to do the same thing as above. Will really appreciate if someone can explain
and if possible correct the code below. The code stops at the line ....Set WBT.... and reports " Runtime error 438" and "Object doesn't
support this property or method !"
Private Sub CommandButton2_Click()
Dim WBT As ThisWorkbook ' This Workbook
Dim WBN As Workbook ' New workbook
Dim WSD As Worksheet ' Keyed in Data
Set WBT = ThisWorkbook("c:\mypc\Desktop\Source.xlsm")
Set WBN = Workbooks("c:\mypc\Desktop\Destination.xlsx")
Set WSD = WBT.Worksheets(1)
WBN.Range("a1") = WSD.Range("a1")
End sub