I want to be able to automatically create a new sheet and in cell A1 of each sheet create a data web connection from a list of URL's on another sheet.

This is the code for creating a Data Web Connection.

    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;https://slp.somerset.gov.uk/_layouts/spusagesite.aspx", 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 = "46,49,50,53,54,56"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
I would need the spreadsheet to do the following:
Create a new sheet
Rename the sheet (based on data in a different sheet [lets call this Sheet A])
Select Cell A1
Create the Data Web Connection based on the URL in sheet A

& Repeat this process, going down the data stored in Sheet A.

I Hope this all makes sense, do let me know if anything is unclear.