Automatic Web Data Query Creation

I am trying to create a macro that will automatically create: sheets and data connections, based on the URLs within one 'central' sheet - rather than manually having to navigate and enter the URL for each sheet. This is what I have so far...

sheets.Add After:=sheets(sheets.Count)

    connstring = Range("data!A1").Value
    With ActiveSheet.QueryTables.Add(Connection:=connstring, _
        Destination:=Range("$A$1"))
        .Name = "spusagesite"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "39,42,43,46,47,49"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

("data!A1")( - Sheet called "data", Cell A1 has an un-hyperlinked url (just text)

Once I can get this working I will also need to make it so that it scrolls through column A in the 'data' sheet to create a sheet/connection for each cell with a URL in.

Any/all advice/help is welcome!

Use Sheets("data").Range("A1").Value to reference the value.

Here's one way to loop through column A:

Sub Loop_A()

    Dim lastRow As Long, row As Long
    
    With Sheets("Sheet1")
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).row
        For row = 1 To lastRow
            If .Cells(row, "A").Value <> "" Then
                MsgBox .Cells(row, "A").Value
            End If
        Next
    End With
    
End Sub