Macro Web Query Speed & Good Structure

HI Guys,

I have a batch of data web query's that collect the data from a stats site which I then manage.

The macro for gathering data is incomplete, but I would like guidance on the best way of completing it.

At the moment I can copy the code over and over for each different player amending the player number and name at the top of the macro but this seems like a waste.

Is there a better way of managing this.

Here's the code

Thanks in advance for any help.

R

Sub Update()
Dim PlayerId As String
Dim UNPId As String

PlayerId = "11801902765"
UNPId = "UNP-Maximus"

With Worksheets(UNPId).QueryTables.Add(Connection:= _
"URL;https://www.novaworld.com/NWCommunities/charStats.aspx?id=" & PlayerId & "&productid=616065&playerCard=1" _
, Destination:=Sheets(UNPId).Range("B100"))
.Name = UNPId
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "14"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

With Worksheets(UNPId).QueryTables.Add(Connection:= _
"URL;https://www.novaworld.com/NWCommunities/charStats.aspx?id=" & PlayerId & "&productid=616065&noPlayerCard=1&viewAllStats=1" _
, Destination:=Sheets(UNPId).Range("F100"))
.Name = _
"charStats.aspx?id=11801902765&productid=616065&noPlayerCard=1&viewAllStats=1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "15"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

With Worksheets(UNPId).QueryTables.Add(Connection:= _
"URL;https://www.novaworld.com/NWCommunities/charStats.aspx?id=" & PlayerId & "&productid=616065&noPlayerCard=1&viewAllStats=1" _
, Destination:=Sheets(UNPId).Range("F150"))
.Name = _
"charStats.aspx?id=11801902765&productid=616065&noPlayerCard=1&viewAllStats=1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "17"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

With Worksheets(UNPId).QueryTables.Add(Connection:= _
"URL;https://www.novaworld.com/NWCommunities/charStats.aspx?id=" & PlayerId & "&productid=616065&noPlayerCard=1&viewAllStats=1" _
, Destination:=Sheets(UNPId).Range("I100"))
.Name = _
"charStats.aspx?id=11801902765&productid=616065&noPlayerCard=1&viewAllStats=1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "18"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

With Worksheets(UNPId).QueryTables.Add(Connection:= _
"URL;https://www.novaworld.com/NWCommunities/charStats.aspx?id=" & PlayerId & "&productid=616065&noPlayerCard=1&viewAllStats=1" _
, Destination:=Sheets(UNPId).Range("Q100"))
.Name = _
"charStats.aspx?id=11801902765&productid=616065&noPlayerCard=1&viewAllStats=1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "23"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

With Worksheets(UNPId).QueryTables.Add(Connection:= _
"URL;https://www.novaworld.com/NWCommunities/charStats.aspx?id=" & PlayerId & "&productid=616065&noPlayerCard=1&viewAllStats=1" _
, Destination:=Sheets(UNPId).Range("Y100"))
.Name = _
"charStats.aspx?id=11801902765&productid=616065&noPlayerCard=1&viewAllStats=1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "28"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

With Worksheets(UNPId).QueryTables.Add(Connection:= _
"URL;https://www.novaworld.com/NWCommunities/charStats.aspx?id=" & PlayerId & "&productid=616065&noPlayerCard=1&viewAllStats=1" _
, Destination:=Sheets(UNPId).Range("AG100"))
.Name = _
"charStats.aspx?id=11801902765&productid=616065&noPlayerCard=1&viewAllStats=1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "33"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With


End Sub