Results 1 to 3 of 3

Thread: Selecting workbook to run macro

  1. #1
    Member
    Join Date
    Aug 2011
    Posts
    92
    Rep Power
    14

    Selecting workbook to run macro

    Hi ,

    What is the best method to select a workbook to run macro? I have tried 2 different codes (Below), but both of them sometime gives error: 'Subscription out of Range'. Also this seems an intermittent problem as sometime it works fine.

    Code:
    Workbooks("Mydata").Activate

    Code:
    Windows("Mydata").Activate
    Thanks
    Rajesh

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    No need to activate. Try to explicitly dim the variables. Try something like

    Code:
    Dim wbkSource   As Workbook
    Dim wbkTarget   As Workbook
    Dim wksSource   As Worksheet
    Dim wksTarget   As Worksheet
    Dim rngTarget   As Range
    
    
    Set wbkSource = ThisWorkbook
    Set wbkTarget = Workbooks("Mydata") 'The 'Mydata' workbook must be open
    
    Set wksSource = wbkSource.Worksheets("Sheet1")
    Set wksTarget = wbkTarget.Worksheets("Data")
    Set rngTarget = wksTarget.Range("A1")
    
    wksSource.Range("A1:D10").Copy rngTarget
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Member
    Join Date
    Aug 2011
    Posts
    92
    Rep Power
    14
    Thanks Admin. It worked

Similar Threads

  1. Open And Activate Workbook Before Runing Macro
    By Howardc in forum Excel Help
    Replies: 5
    Last Post: 06-04-2013, 07:23 PM
  2. Replies: 7
    Last Post: 05-08-2013, 07:12 PM
  3. Replies: 0
    Last Post: 04-20-2013, 10:07 AM
  4. How To Save Macro To Personal Workbook
    By NITIN SHETTY in forum Excel Help
    Replies: 1
    Last Post: 04-07-2013, 01:07 PM
  5. Calling another macro in same workbook
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 4
    Last Post: 08-18-2012, 12:20 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •