VBA code to copy table from webpage into Excel

Question

I have modified the code to try to get a sequence of similar tables. However, these tables copied into respective sheets are exactly the same, that is, the table for the first variable/sheet has been replicated to the other sheets which are created for different variables - the tables should be different on different sheets. What's wrong with my new code? Your advice again would be very appreciated!

Sub CopyWebTable()

    Dim IE As InternetExplorer, hTable As Object, clipboard As Object, t As Date
    Dim Var As String
    Const MAX_WAIT_SEC As Long = 5

    For i = 1 To 3
        Var = ThisWorkbook.Worksheets("Par").Range("B" & i + 2)

        Set clipboard = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        Set IE = New InternetExplorer

        With IE
            .Visible = True
            .Navigate2 "https://eresearch.fidelity.com/eresearch/evaluate/fundamentals/earnings.jhtml?tab=details&symbols=" & Var

            While .Busy Or .readyState < 4: DoEvents: Wend

            t = Timer                            'timed loop for details table to be present
            Do
                On Error Resume Next
                Set hTable = IE.document.querySelector(".earningsHistoryTable-Cont table")
                On Error GoTo 0
                If Timer - t > MAX_WAIT_SEC Then Exit Do
            Loop While hTable Is Nothing
            If Not hTable Is Nothing Then        'use clipboard to copy paste
                clipboard.SetText hTable.outerHTML
                clipboard.PutInClipboard
                ThisWorkbook.Worksheets(Var).Range("A1").PasteSpecial

            End If
        End With
    Next i

End Sub

Answer 1

Try the following construct where we move the loop over vars inside the creation of the IE object and ensure hTable is always set to nothing before looping round again.

Option Explicit

Sub CopyWebTable()

    Dim IE As InternetExplorer, hTable As Object, clipboard As Object, t As Date
    Dim Var As String
    Const MAX_WAIT_SEC As Long = 5
    Set clipboard = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    Set IE = New InternetExplorer

    With IE
        .Visible = True

        For i = 1 To 3
            Var = ThisWorkbook.Worksheets("Par").Range("B" & i + 2)

            .Navigate2 "https://eresearch.fidelity.com/eresearch/evaluate/fundamentals/earnings.jhtml?tab=details&symbols=" & Var

            While .Busy Or .readyState < 4: DoEvents: Wend

            t = Timer                            'timed loop for details table to be present
            Do
                On Error Resume Next
                Set hTable = .document.querySelector(".earningsHistoryTable-Cont table")
                On Error GoTo 0
                If Timer - t > MAX_WAIT_SEC Then Exit Do
            Loop While hTable Is Nothing
            If Not hTable Is Nothing Then        'use clipboard to copy paste
                clipboard.SetText hTable.outerHTML
                clipboard.PutInClipboard
                ThisWorkbook.Worksheets(Var).Range("A1").PasteSpecial
                Set hTable = Nothing
            End If
        Next i
    End With
End Sub