I am doing a project that requires a large amount of data. I have about 19,000 separate URL links (A1:A19000), and each of those list goes to a site with several tables (the table I need is roughly 26 columns and 100 rows long). Essentially, I need a macro that takes a URL from my first cell (www.somedomain.com/x/y/z/variable1) and copies down that table in cells C1:AC101, then goes to cell A2 and repeats the same process for the table in that link (www.somedomain.com/x/y/z/variable2) in C101:AC201, and does that for as many links as it can before hitting the max number of rows.

I think I know enough to do this if I only wanted a single row from those tables, but I'm not sure how to get it to work for something like this. So I guess my questions are:

1. What would I need to do to pull the URL I'm going to query from a specific cell?
2. How can I automate going down one row to go to the next URL but going down 100 rows per table so nothing gets overwritten?

Here is the code of a simple query of the said link and table.

Sub WebQuery()
'
' WebQuery Macro
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.somedomain.com/x/y/z/variable1", _
Destination:=Range("$C$1"))
.Name = "1991"
.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 = """pgl_basic"""
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

More information: The URLs have a static section, www.somedomain.com/x/y/z/, and then a changing portion. That changing portion isn't a number that goes 1, 2, 3, but it something more like x/xialbers01, where the x is related to the first part of the xialbers portion and 01 is used to identify duplicates (xialbers02 and xialbers03). Then you have something like word/year, where word is static and the year goes up sequentially. For example:

www.somedomain.com/x/y/z/a/abcdefg01/word/1991
www.somedomain.com/x/y/z/a/abcdefg01/word/1992
www.somedomain.com/x/y/z/b/bcdefgh01/word/1945
www.somedomain.com/x/y/z/b/bcdefgh02/word/2008
www.somedomain.com/x/y/z/b/bcdefgh02/word/2009
www.somedomain.com/x/y/z/b/bcdefgh02/word/2010