Stay in the active workbook after opening other workbooks

user_10175920 0
Viewed 2122

I am trying to open two other Excel workbooks to take data to do my calculation through the macro. I have a working code to open those files.

Dim wbCount As Integer
Application.ScreenUpdating = False
For wbCount = 1 To 2
Workbooks.Open Filename:=ThisWorkbook.Path & "\Installation Sheet " & wbCount & ".xlsm", UpdateLinks:=False
Next wbCount
ThisWorkbook.Activate
Application.ScreenUpdating = True

The other files are named "Installation Sheet 1" and "Installation Sheet 2". After this code runs, I end up with "Installation Sheet 1" displaying instead of my active workbook.

vba excel
3 Answers

Put your ActiveWorkbook into a variable at the appropriate place and then use that to move back. ThisWorkbook is the one with code in. So be sure if you mean ActiveWorkbook or ThisWorkbook, or if ThisWorkbook is the ActiveWorkbook at the time. In that case, you can simply Set wb = ThisWorkbook

Dim wb As Workbook
Set wb = ActiveWorkbook
'Set wb = ThisWorkbook   '<== If you want the workbook containing the code
'Other code
wb.Activate
user_62412350

You have to put your workbooks references into variables and then using these variables you have two options: close newly opened workbook, so only original stays opened or use Activate method to bring the original workbook to front.

Sample code:

Sub OpeningWb()
    Dim wb As Workbook, currentWb As Workbook
    Set currentWb = ThisWorkbook
    Set wb = Workbooks.Open("your path here")
    ' if you want to close opened workbook
    'wb.Close
    ' if you want to bring to front original workbook
    currentWb.Activate
End Sub

Alternatively, you can do the following:

Dim wbToDisplay As Workbook
Set wbToDisdplay = Workbooks("workbook name here")
wbToDisplay.Activate
user_71325500

The above solutions didn't work for me, because activate method is fired before second workbook is displayed, so I get active this second workbook always. My workaround is wait a couple of seconds before calling Activate method:

Dim t: t = Timer
While Timer < t + 2 'wait for 2 seconds
    DoEvents
Wend
ThisWorkbook.Activate
user_16830400
Related