Announcement

Collapse
No announcement yet.

Substitute macro with own code

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

  • Tanah
    started a topic Substitute macro with own code

    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

  • Kenneth Hobson
    replied
    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.

    Leave a comment:


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

    Leave a comment:


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

    Leave a comment:

Working...
X