Activate worksheet after opening multiple password protected workbooks

Question

I have some code for opening multiple password protected workbooks. At the end of the code I am attempting to reactivate the worksheet in the master workbook. However, it does not reactivate. Possibly due to opening process taking longer that the sub to run? Any suggestions?

I have tried turning off events. Also attempted a wait function.

Public Sub OpenFiles()

Dim mainwb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim numClients
Dim pw As String
Dim i As Integer
Dim newHour As Variant
Dim newMinute As Variant
Dim newSecond As Variant
Dim waitTime As Variant

Set mainwb = ThisWorkbook

myPath = mainwb.Sheets("Dashboard").Range("C1")
myExtension = ".xlsx"
numClients = mainwb.Sheets("Dashboard").Range("I1")

'Optimize Macro Speed
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual

For i = 4 To numClients

    myFile = ThisWorkbook.Sheets("Dashboard").Range("A" & i).Value
    pw = ThisWorkbook.Sheets("Dashboard").Range("B" & i).Value
    Workbooks.Open Filename:=myPath & myFile & myExtension, Password:=pw

    DoEvents

Next i

    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic

ThisWorkbook.Worksheets("Dashboard").Activate

End Sub

The activated worksheet is the sheet on the last opened workbook.

Instead of using Thisworkbook call the workbook out by name, that way it know which to activate. This is simply using whichever is currently active and going to that tab. — Mark S.

Mark S. Mark S. · Accepted Answer · 2019-07-26T19:28:07

I modified your code to activate the specific workbook but you need to put the workbook name in.

Dim mainwb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim numClients
Dim pw As String
Dim i As Integer
Dim newHour As Variant
Dim newMinute As Variant
Dim newSecond As Variant
Dim waitTime As Variant

Set mainwb = ThisWorkbook

myPath = mainwb.Sheets("Dashboard").Range("C1")
myExtension = ".xlsx"
numClients = mainwb.Sheets("Dashboard").Range("I1")

'Optimize Macro Speed
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual

For i = 4 To numClients

    myFile = ThisWorkbook.Sheets("Dashboard").Range("A" & i).Value
    pw = ThisWorkbook.Sheets("Dashboard").Range("B" & i).Value
    Workbooks.Open Filename:=myPath & myFile & myExtension, Password:=pw

    DoEvents

Next i

    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic

 Workbooks("YourWorkbookName.xlsx").Activate 'Put your workbook name here. can add worksheet as well

End Sub````

Activate worksheet after opening multiple password protected workbooks

1 Answers