Ah, I see. I was picturing logic that works roughly like this:But you have in mind doing it this way:Well, that should work too. You may have an additional complication detecting the end of each page and the beginning of the next, but it probably won't be difficult, much less insurmountable. So, ok, we'll go that way:

As usual, there are a couple of ways. You were already doing it the first time, sort of: Using RefreshStyle=xlInsertDeleteCells, every time you queried the next page Excel inserted a column, moving the previous page's data rightward to column L, and put the new page in column K. I changed the option to xlOverwriteCells—which caused it to write over the old data rather than preserving it—because I was thinking you would eventually write logic inside the loop to grab all the downloaded data before you went around for the next page.

If you want to grab all the pages, then move all their data around, that has to change. I thought it would be easy: Just use the same QueryTable, changing the Destination each time the loop iterates. It turns out, though (I played with it today) that the Destination property is read-only; it's set at QueryTable.Add time and cannot be changed. So if you're going to run all the pages before rearranging any of them, we'll have to preserve all the data by putting each page in a different Destination in the worksheet.

You were trying to do that when you found the last row and set a new range for the Destination; you just did it wrong. You were thinking that if you set the second range to K1:K<lastrow>, Excel would automatically put the second page after that. But no; the second query goes in K1, because after all that's what it thought you were telling it to do. Here's what you do instead: set the Destination to K<lastrow+1>. In my program I did it this way:By the way, once you use the For...Next statement, you don't need to increment Y any more. You're doing this:The loop itself is already adding 1 to Y each time it executes. Your additional statement ("Y = Y + 1" statement) is causing the program to increment Y twice in each loop; in effect it's fetching every other page, ie 1, 3, 5 and so on; you're missing half the results and getting only 10 pages. Drop the extra statement.