Private WithEvents objIE1 As SHDocVw.InternetExplorer
Private objIE2 As SHDocVw.InternetExplorer

Private Sub Worksheet_Activate()
    Dim strURL As String
    Const navOpenInNewWindow =  1 

    Set objIE1 = CreateObject("InternetExplorer.Application")
    
    objIE1.Visible = True
    objIE1.navigate "www.yandex.ru"
    
    While objIE1.Busy
        DoEvents
    Wend
    
    strURL = objIE1.document.Links( 20 ).href
        
    objIE1.navigate strURL, navOpenInNewWindow
    objIE2.navigate "www.rambler.ru"
    
    While objIE2.Busy
        DoEvents
    Wend
    
    MsgBox objIE2.LocationURL
End Sub

Private Sub objIE1_NewWindow2( _
  ByRef ppDisp As Object, _
  ByRef Cancel As Boolean)
    Set objIE2 = CreateObject("InternetExplorer.Application")
    Set ppDisp = objIE2.Application
End Sub