Need Macro to pull data from list and create new worksheets for each result

[kingsdime29x]

I am trying to modify a macro I have that will help me use the macro below to help me go through a list of players to pull all the data tables mentioned below into one worksheet per player and then move on to the next player with their stats on the next worksheet. Right now I am going through each player one by one with the macro below and takes a lot of time and trying to see if there is a quicker solution.

I have attached the list of players in the Excel file and then attached a notepad file of the query file that I used in Excel. Below is the code that I am currently using.

Sub E()
'
' E Macro
'
' Keyboard Shortcut: Ctrl+Shift+E
'
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.QueryTables.Add(Connection:= _
        "FINDER;C:\Users\mlaporte\Dropbox\NFL\NFL Players\E\All - E.iqy", Destination _
        :=Range("$A$1"))
        .Name = "All - E_1"
        .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 = _
        """defense"",""games_played"",""kicking"",""returns"",""passing"",""receiving_and_rushing"",""rushing_and_receiving"",""scoring"""
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

Any help on how to speed up this process would be greatly appreciated. Thank you in advance.

[natefarm]

How would your code be linked to an individual player's name from the spreadsheet?

[Leith Ross]

Hello kingsdime29x,

Welcome to the Forum!

This was quite a challenge. Good news is you have a working solution. The workbook attached contains several macros to pull the stat data for each player on "Sheet1".

As each player's stats are pulled, a new worksheet is added to the workbook. Each sheet is named after the player.

The tables appear as the do on the web page, complete with working hyperlinks. The table are placed one on top of the other on the worksheet separated by a blank row.

Sheet1 has a button to run the macros. Column "B" of the sheet has been reserved for error logging. Errors like connection problems to the site or duplicate player names in the list.

Nollaig Chridheil!
(Merry Christmas!)