+ Reply to Thread
Results 1 to 9 of 9

Macro to activate 2 workbooks

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    03-16-2004
    MS-Off Ver
    2016
    Posts
    175

    Macro to activate 2 workbooks

    I recorded this macro in Excel 2016 with the macro recorder and want to insert vba . Of course the two files were opened already when the macro was recorded. I am poor in VBA and after looking at many examples of how to do this online cannot get it to work. Here is the Macro recorder's version.

    Sub openwkbks()
        
        Windows("Aqu010216.xlsm").Activate
        Sheets("Brfig").Select
        Range("B7:B15").Select
        Selection.Copy
        Windows("Matrix.xlsm").Activate
        Range("C5").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=True
            Selection.Columns.AutoFit
        Windows("Aqu010216.xlsm").Activate
        Range("D7:D15").Select
        Selection.Copy
        Windows("Matrix.xlsm").Activate
        Range("C6").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=True
            Windows("Aqu010216.xlsm").Activate
        Range("F7:F15").Select
        Selection.Copy
        Windows("Matrix.xlsm").Activate
        Range("C7").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=True
        Windows("Aqu010216.xlsm").Activate
        Range("H7:H15").Select
        Selection.Copy
         Windows("Matrix.xlsm").Activate
        Range("C8").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=True
        Windows("Aqu010216.xlsm").Activate
        Range("J7:J15").Select
        Selection.Copy
        Windows("Matrix.xlsm").Activate
        Range("C9").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=True
         Windows("Aqu010216.xlsm").Activate
         Sheets("Points").Select
         Range("E7:E15").Select
         Selection.Copy
         Windows("Matrix.xlsm").Activate
        Range("C10").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=True
        Windows("Aqu010216.xlsm").Activate
        Range("g7:g15").Select
         Selection.Copy
         Windows("Matrix.xlsm").Activate
        Range("C11").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=True
        Windows("Aqu010216.xlsm").Activate
        Range("i7:i15").Select
         Selection.Copy
         Windows("Matrix.xlsm").Activate
        Range("C12").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=True
        Windows("Aqu010216.xlsm").Activate
        Range("n7:n15").Select
         Selection.Copy
         Windows("Matrix.xlsm").Activate
        Range("C13").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=True
        Windows("Aqu010216.xlsm").Activate
        Range("p7:p15").Select
         Selection.Copy
         Windows("Matrix.xlsm").Activate
        Range("C14").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=True
                 
        End Sub
    The"Matrix" workbook has this code and where the data needs to be transferred to. The "Aqu010216.xlsm" is but one workbook of many with different names that will need to be opened (not simultaneously) to feed the Matrix workbook

  2. #2
    Forum Contributor
    Join Date
    12-06-2012
    Location
    sacramento,calif
    MS-Off Ver
    Excel 2010
    Posts
    217

    Re: Macro to activate 2 workbooks

    Horse Racing? PM me.
    Please click on * Add Reputation if my solution was useful, thanks

  3. #3
    Forum Contributor
    Join Date
    03-16-2004
    MS-Off Ver
    2016
    Posts
    175

    Re: Macro to activate 2 workbooks

    Yes it has to do with horse racing but it's still a spreadsheet like any other type.

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro to activate 2 workbooks

    Hi Light

    See if it still works:

    Sub light_openwkbks(): Dim wb As Workbook, M As Workbook, wm As Worksheet, ws As Worksheet
    
    Set M = Workbooks("Matrix.xlsm"): Set wm = M.wm.Sheets(ActiveSheet.Name)
    Set wb = Workbooks("Aqu010216.xlsm")   'Change Book Name for each pass
    Set ws = wb.Sheets("Brfig")
    
    ws.Range("B7:B15").Copy: wm.Range("C5").PasteSpecial Paste:=xlPasteValues, Transpose:=True
    
    ws.Range("D7:D15").Copy: wm.Range("C6").PasteSpecial Paste:=xlPasteValues, Transpose:=True
    
    ws.Range("F7:F15").Copy: wm.Range("C7").PasteSpecial Paste:=xlPasteValues, Transpose:=True
    
    ws.Range("H7:H15").Copy: wm.Range("C8").PasteSpecial Paste:=xlPasteValues, Transpose:=True
    
    ws.Range("J7:J15").Copy: wm.Range("C9").PasteSpecial Paste:=xlPasteValues, Transpose:=True
        
                    Dim wp As Worksheet: Set wp = wb.Sheets("Points")
         
    wp.Range("E7:E15").Copy: wm.Range("C10").PasteSpecial Paste:=xlPasteValues, Transpose:=True
         
    wp.Range("G7:G15").Copy: wm.Range("C11").PasteSpecial Paste:=xlPasteValues, Transpose:=True
        
    wp.Range("I7:I15").Copy: wm.Range("C12").PasteSpecial Paste:=xlPasteValues, Transpose:=True
         
    wp.Range("N7:N15").Copy: wm.Range("C13").PasteSpecial Paste:=xlPasteValues, Transpose:=True
         
    wp.Range("P7:P15").Copy: wm.Range("C14").PasteSpecial Paste:=xlPasteValues, Transpose:=True
         
                                wm.Columns.AutoFit
     
        End Sub
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  5. #5
    Forum Contributor
    Join Date
    03-16-2004
    MS-Off Ver
    2016
    Posts
    175

    Re: Macro to activate 2 workbooks

    Thanks xladept. I get a "runtime error "438" Object doesn't support this property or method". Using debug it comes up after the line

    Set wm = M.wm.Sheets(ActiveSheet.Name) is executed.

    I've inserted the code in the module where the original macro recorder code was and also tried inserting in the "Matrix" sheets and get the same error message in all cases.

  6. #6
    Forum Contributor
    Join Date
    12-06-2012
    Location
    sacramento,calif
    MS-Off Ver
    Excel 2010
    Posts
    217

    Re: Macro to activate 2 workbooks

    should be:

    Set wm = M.Sheets(ActiveSheet.Name)

  7. #7
    Forum Contributor
    Join Date
    03-16-2004
    MS-Off Ver
    2016
    Posts
    175

    Re: Macro to activate 2 workbooks

    Thank You JFGF. That worked when I made "Matrix" the active sheet.

  8. #8
    Forum Contributor
    Join Date
    12-06-2012
    Location
    sacramento,calif
    MS-Off Ver
    Excel 2010
    Posts
    217

    Re: Macro to activate 2 workbooks

    Quote Originally Posted by light View Post
    Thank You JFGF. That worked when I made "Matrix" the active sheet.
    yw, I sent PM be back in a few.

  9. #9
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro to activate 2 workbooks

    Sorry for the silly mistake

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Wildcard text to activate workbooks name
    By Faridwahidi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-03-2014, 02:38 AM
  2. can I activate workbooks without writing their names in code?
    By lubbamkt in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 06-11-2014, 12:55 PM
  3. Replies: 2
    Last Post: 02-28-2013, 11:39 AM
  4. How can I activate specific workbooks
    By Launchnet in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 02-24-2009, 09:12 PM
  5. Workbooks(WorkbookName).Activate Failure
    By [email protected] in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-16-2006, 07:20 AM
  6. how do I activate links without opening external workbooks?
    By teh_chucksta in forum Excel General
    Replies: 0
    Last Post: 08-29-2005, 07:05 PM
  7. Activate Variable Named Workbooks
    By HelpMe in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-20-2005, 11:17 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1