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!