Announcement

Collapse
No announcement yet.

Substitute macro with own code

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

  • #2
    ThisWorkbook is the workbook that the macro is in. If that was your intent, just use ThisWorkbook rather than workbook variable WBT.

    Comment


    • #3
      Hi Kenneth, thank you for your response and for a moment there I thought avoiding the variable WBT was the answer but sadly after trying for about half and hour
      with various changes I am still in the dark.

      Anyway just for your info I got the code from this article " http://ww2.cfo.com/spreadsheets/2011...n-excel-macro/ " which appears elegant but does not work,,at least for me.

      Thanks anyway.

      Comment


      • #4


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

        Code:
        Set WBT = ThisWorkbook     'c:\mypc\Desktop\Source.xlsm
        Set WBN = Workbooks("Destination.xlsx")
        A recorded macro can be made more efficient. e.g.
        Code:
        Private Sub CommandButton2_Click()
          Range("A1:A3")..Cut 
          Workbooks("Destination.xlsx").Activate
          ActiveSheet.Paste
          Worksheets("Source.xlsm").Activate
        End sub
        Normally, one references the workbook.worksheet.range sort of thing. There is seldom a need to use Select, Selection, Activate.

        Comment

        Working...
        X