Activate different workbook to run macro

0

votes

I created a macro in my workbook using an active worksheet from another workbook. I would now like to run my macro, but use yet another different active workbook to get my data.

I have this line 20 times in my macro...

Windows("IFS_round_1").Activate

...so I do not want to change it (ex. IFS_round_2) each time I open a new workbook to run the macro. Is there something I can add so the macro just uses whichever active workbook I have open?

Thanks!

excel

0

votes

Create a variable to refer to the workbook. For example:

Sub Macro1()
    Dim wb as Workbook
    Set wb = ActiveWorkbook

    wb.Activate

    'DO CODE HERE
End Sub

Does this help?

0

votes

I don't know if you are opening the other workbook programatically, but this solution works. basically you just save the handle to the other workbook as soon as you open it.

sother_filename = "IFS_round_1"
'saves the name of the current workbook
curr_workbook = ActiveWorkbook.Name
'opens the new workbook, this automatically returns the handle to the other
'workbook (if it opened it successfully)
other_workbook = OpenWorkbook(sother_filename)

But what fun is that? one more solution to automatically get the workbook name when there are only two workbooks open and then simply use that to call the other workbook

Function GetOtherWBName()
    GetOtherWBName = ""

    'if we dont have exactly two books open
    'we don't know what to do, so just quit
    If (Workbooks.Count) <> 2 Then
        Exit Function
    End If

    curr_wb = ActiveWorkbook.Name

    'if the active workbook has the same name as workbook 1
    If (StrComp(curr_wb, Workbooks(1).Name) = 0) Then
        'then the other workbook is workbook 2
        GetOtherWBName = Workbooks(2).Name
    Else
        'then this is the other workbook
        GetOtherWBName = Workbooks(1).Name
    End If

End Function

So now in the workbook that has the macro make a button and assign a macro to it similar to this

Sub ButtonClick()
    'first we save the current book, so we can call it easily later
    curr_wb = ActiveWorkbook.Name

    other_wb = GetOtherWBName()

    If Len(other_wb) = 0 Then
        MsgBox ("unable to get other wb")
        Exit Sub
    End If

    'now to call the other workbook just use
    Workbooks(other_wb).Activate

    'all the rest of your code
End Sub

Activate different workbook to run macro

2 Answers