Run A Macro Against a Table Until it gets to bottom

[racer25]

First of all apologies for the title not sure how to reference this.

I have a table which contains a username and id number. For each
username I have a sheet with the same name.

Here is a few records from the table Column A and Column B

UNP-1ce 2168950611
UNP-ali169 111667564008
UNP-Bailey 111686165248
UNP-Bala420 27498979167
UNP-Blade 24241061987

I I am running a web query for each person where the difference in the
URL is the id number

Here is the code of the macro I use

Sub Update_Player()

Dim PlayerId As String
Dim PlayerName As String

PlayerId = "11801902765"
PlayerName = "UNP-Maximus"

With
Worksheets(PlayerName).QueryTables.Add(Connection:="URL;http://www.stupidhero.com/DFA/SigConfig.cfm?PlayerID="
& PlayerId & "" _
, Destination:=Sheets(PlayerName).Range("A2"))
.Name = PlayerName
.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 = "13"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

End Sub

In theory I could replicate this macro over and over changing the name
and code but this seems rather bad

Is there anyway that I can get the macro to look at the contents of the
sheet with the name and code in it run the macro against row 1 then row
2 until the end.

Hopefully this makes sense,

Thanks in advance

Rob

 

[Guest]

Assume your table is in standard format: no blank lines in between lines and
has a blank row beneath, blank column to the right of the data range modified
sub below, my additions don't have the >:

Sub Update_Player()

Dim ListRange as Range, CurrentRow as Range

Dim PlayerId As String
Dim PlayerName As String

' Next line finds your data table (substitute actual sheet name):
Set ListRange = Worksheets("SheetName").Range("A1").CurrentRegion

'Now loop through the list:
For Each CurrentRow in ListRange.Rows

' Get the player ID and name:
PlayerName = CurrentRow.Cells(1,1)
PlayerId = CurrentRow.Cells(1,2)

' Eliminate the lines where you assigned the name & id

' This part should stay the same:

With
Worksheets(PlayerName).QueryTables.Add(Connection:="URL;http://www.stupidhero.com/DFA/SigConfig.cfm?PlayerID="
& PlayerId & "" _
, Destination:=Sheets(PlayerName).Range("A2"))
.Name = PlayerName
.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 = "13"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

' Next line in list:
Next CurrentRow

End Sub

 

[racer25]

Thank you very much appreciate that

Works a treat