Automatic Web query from multiple links in cells

[yukisome]

Greetings to all,

I am new to the forum and are interested in learning about VBA/Macros.

Here I have a small problems about automatic web query. Hope the teachers here can help. Great Thanks.

Question: How do I automatic the web query process for all the links in my workbook.

In my workbook(attached), I have the worksheet"URL".
In worksheet "URL", cell A1 to A574 contains link that I capture from a website.
The links all have similar format of http://www.reb.......company=51. The number "51" is a unique id for a company.
A change of that number will lead to different company's roster.
The URLs will open up pages with very similar structure, each page contains one table of member roster.
I need to capture that the roster table from each of the links and paste them onto a new worksheet. (sheet8 in my workbook is a example result)

I only know how to do one web query at a time and don't have enough knowledge to modify the code correctly.

I had recorded my macro for one links, and below are the codes.

Sub Scrape()
'
' Scrape Macro
'
' Keyboard Shortcut: Ctrl+s
'
    Range("A2").Select
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.rebny.com/RLS_company_results.jsp?company=51", Destination:= _
        Range("$A$1"))
        .Name = "RLS_company_results.jsp?company=51_2"
        .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 = "9"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub