VBA to run hyperlinks that returns to the original Workbook

[gberg]

I have three workbooks all in SharePoint folders. The main Workbook has a macro to open the other two Workbooks. The Hyperlinks to the other two Workbooks is in a tab called "Data". I have the following macro that works fine for opening the two other Workbooks but I want it to return to the original workbook and have a code at the end for it (highlighted in red) but it does not run, the last Workbook opened stays active. If I run the last line as a separate macro it works fine. Any ideas?

Sub Open_workbook()

Dim xHyperlink As Hyperlink
Dim WorkRng As Range
On Error Resume Next
'Set Range
Sheets("Data").Activate
Range("Data_Weekly_Spreadsheet:Data_Job_Log").Select
Set WorkRng = Application.Selection
For Each xHyperlink In WorkRng.Hyperlinks
xHyperlink.Follow
Next

Windows("JPR - Monthly Tracker 2022 - w Ext Links.xlsm").Activate

End Sub

Thanks

 

[Dan_W]

So this macro is run from the Main workbook, which is the one you're trying to activate? If so, you could try:

AppActivate ThisWorkbook.Windows(1).Caption

instead of the line in red.

Or if the workbook is the one your code is trying to activate - "JPR - Monthly Tracker 2022 - w Ext Links.xlsm" - and it isn't working, you could try:
AppActivate Workbooks("JPR - Monthly Tracker 2022 - w Ext Links.xlsm").Windows(1).Caption

Alternatively, it might be the case that VBA is moving to quickly for Excel to keep up, in which case you would need to get the code to pause before proceeding to the final line that shifts focus back to the main workbook. Let me know how it goes.

 

[gberg]

Thanks for the feedback. It appears to be option 2, the VBA is moving to fast. I put in the pause as you suggested and that worked.