How to modify a VBA to place all data on one page instead of a new page each query

[PistachioPedro]

Hello! Please help me, this is the code I have so far which I have found online.

Private Const URL_TEMPLATE As String = "URL;http://www.nhl.com/ice/playerstats.htm?fetchKey=20142ALLSASALL&viewName=assists&sort=player.bioFirstNameLastName&pg={0}"
Private Const NUMBER_OF_PAGES As Byte = 15

Sub test()
    Dim page As Byte
    Dim queryTableObject As QueryTable
    Dim url As String

    For page = 1 To NUMBER_OF_PAGES
        url = VBA.Strings.Replace(URL_TEMPLATE, "{0}", page)
        Set queryTableObject = ActiveSheet.QueryTables.Add(Connection:=url, Destination:=ThisWorkbook.Worksheets.Add.[a1])
        queryTableObject.WebSelectionType = xlSpecifiedTables
        queryTableObject.WebTables = "6"
        queryTableObject.Refresh
    Next page

End Sub

The only modification I have made is change the WebTables and the target URL. How can I edit this so that each new page query will add the returned data on the same sheet, underneath the previous page?

As it is now it creates a new worksheet for every page query. Thanks!

[TMS]

This gets them all on the same worksheet.

Option Explicit

Private Const URL_TEMPLATE As String = "URL;http://www.nhl.com/ice/playerstats.htm?fetchKey=20142ALLSASALL&viewName=assists&sort=player.bioFirstNameLastName&pg={0}"
Private Const NUMBER_OF_PAGES As Byte = 15

Sub test1()
' down the page ...
Dim page As Long
Dim queryTableObject As QueryTable
Dim url As String

For page = 1 To NUMBER_OF_PAGES
    url = VBA.Strings.Replace(URL_TEMPLATE, "{0}", page)
    Set queryTableObject = _
        ActiveSheet.QueryTables.Add(Connection:=url, _
        Destination:=ThisWorkbook.ActiveSheet.Cells((page - 1) * 40 + 1, 1))
    queryTableObject.WebSelectionType = _
        xlSpecifiedTables
    queryTableObject.WebTables = "6"
    queryTableObject.Refresh
    
Next page

End Sub

Sub test2()
' across the page
Dim page As Long
Dim queryTableObject As QueryTable
Dim url As String

For page = 1 To NUMBER_OF_PAGES
    url = VBA.Strings.Replace(URL_TEMPLATE, "{0}", page)
    Set queryTableObject = _
        ActiveSheet.QueryTables.Add(Connection:=url, _
        Destination:=ThisWorkbook.ActiveSheet.Cells(1, (page - 1) * 26 + 1))
    queryTableObject.WebSelectionType = _
        xlSpecifiedTables
    queryTableObject.WebTables = "6"
    queryTableObject.Refresh
    
Next page

End Sub

Regards, TMS

[TMS]

Slight refinement. Every 36 rows and fit to page width.

Option Explicit

Private Const URL_TEMPLATE As String = "URL;http://www.nhl.com/ice/playerstats.htm?fetchKey=20142ALLSASALL&viewName=assists&sort=player.bioFirstNameLastName&pg={0}"
Private Const NUMBER_OF_PAGES As Byte = 15

Sub test3()
' down the page ...
Dim page As Long
Dim queryTableObject As QueryTable
Dim url As String

For page = 1 To NUMBER_OF_PAGES
    url = VBA.Strings.Replace(URL_TEMPLATE, "{0}", page)
    Set queryTableObject = _
        ActiveSheet.QueryTables.Add(Connection:=url, _
        Destination:=ThisWorkbook.ActiveSheet.Cells((page - 1) * 36 + 1, 1))
    queryTableObject.WebSelectionType = _
        xlSpecifiedTables
    queryTableObject.WebTables = "6"
    queryTableObject.Refresh
Next page

With ActiveSheet.PageSetup
    .Orientation = xlPortrait
    .PaperSize = xlPaperA4
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = False
End With

End Sub

Regards, TMS