Using Query Wizard to generate .iqy files

Probably. Can you provide the working files to test this out?

1) Master workbook with 3 rows filled out A & B
2) Copies of the 3 workbooks you created from manually pulling them down.

With that, we can work on a VBA solution and know when we get it right. Then when you expand your MASTER out to many more rows it should keep working.

Web Query does not pull in the info from the hidden tables. I'll ping some others more versed in HTML scraping to take a look.

This code was offered by another member anonymously:

Dim dws As Worksheet

Sub ScrapingWebTableData()
Dim sws As Worksheet
Dim aRng As Range, aCell As Range
Dim lr As Long
Dim IE As Object
Dim doc As Object
Dim strURL As String
Application.ScreenUpdating = False
Set sws = Sheets("Sheet1")
lr = sws.Cells(Rows.Count, 1).End(xlUp).Row
Set aRng = sws.Range("A1:A" & lr)
For Each aCell In aRng
   Application.StatusBar = "Getting data for " & aCell.Value
   On Error Resume Next
   Set dws = Sheets(aCell.Value)
   dws.Cells.Clear
   On Error GoTo 0
   If dws Is Nothing Then
      Sheets.Add(after:=Sheets(Sheets.Count)).Name = aCell.Value
      Set dws = ActiveSheet
   End If
   strURL = aCell.Offset(0, 1).Value
   Set IE = CreateObject("InternetExplorer.Application")
   With IE
      .Visible = False
      .navigate strURL
      Do: DoEvents: Loop Until .readyState = 4
      Application.Wait Now + TimeValue("00:00:03")
      Set doc = IE.document
      GetTableData doc
      .Quit
   End With
Next aCell
sws.Activate
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub

Sub GetTableData(doc As Object)
Dim ws As Worksheet
Dim rng As Range
Dim tbl As Object, rw As Object, cl As Object
Dim r As Long, i As Long

For Each tbl In doc.getElementsByTagName("table")
    tblIndex = tblIndex + 1
    If tblIndex = 3 Then
    r = r + 1
    Set rng = dws.Range("A" & r)
    For Each rw In tbl.Rows
        For Each cl In rw.Cells
            rng.Value = cl.outerText
            Set rng = rng.Offset(, 1)
            i = i + 1
        Next cl
        r = r + 1
        Set rng = rng.Offset(1, -i)
        i = 0
    Next rw
    End If
Next tbl
dws.Cells.ClearFormats
Set dws = Nothing
End Sub

Insert a blank code module and install that code. Then run ScrapingWebTableData.