Substitute macro with own code

  • 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.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

  • If WBN is open, use the base name. Excel only allows one base name open at once.

    1. Set WBT = ThisWorkbook 'c:\mypc\Desktop\Source.xlsm
    2. Set WBN = Workbooks("Destination.xlsx")

    A recorded macro can be made more efficient. e.g.

    1. Private Sub CommandButton2_Click()
    2. Range("A1:A3")..Cut
    3. Workbooks("Destination.xlsx").Activate
    4. ActiveSheet.Paste
    5. Worksheets("Source.xlsm").Activate
    6. End sub

    Normally, one references the workbook.worksheet.range sort of thing. There is seldom a need to use Select, Selection, Activate.