VBA QueryTables loops class reference

What I am trying to do is grab data form mutable URLs, at the same time using cells A1 to A10 as the last string of text on the URLs QueryTables.

• Example
  Cell A1= B0006SH4PA

• URL or QueryTables would changed in reference to cells text

  myURlocation=B0006SH4PA ' then data scraped from this URL then loop to next cell down

• This would continue to cell A10 with each cell having different test making a different QueryTable for every cell.

This is code I have at the moment

Sub URL_Static_Query()
Dim i As Integer
   
   With ActiveSheet.QueryTables.Add(Connection:= _
      "URL;myURlocation=" & Range("a1"), _
         Destination:=Range("a1"))
   
      .BackgroundQuery = True
      .TablesOnlyFromHTML = True
      .Refresh BackgroundQuery:=False
      .SaveData = True
   End With
End Sub

Try this, it will write the data starting from column B below each other.

  Dim i As Long
  For i = 1 To 10
     With Sheet1.QueryTables.Add(Connection:="URL;myURlocation=" & Range("a" & i), _
        Destination:=sheet1.Range("b999999").End(xlUp).Offset(1))

        .BackgroundQuery = True
        .TablesOnlyFromHTML = True
        .Refresh BackgroundQuery:=False
        .SaveData = True
     End With
  Next i