Display data horizontally not vertically through VBA

Hi,

I have built a macro which loops through data from one sheet ("Sheet1") to build a URL (it's an API for the web analytics tool Webtrends). The macro then loads data from the API into a new sheet "display". Each dataset has the same 4 headers: Time Period, Page, Title, Page Views.

My issue is that the looped data is being displayed along columns (horizontally), but I need the data to display row after row (vertically).

The result I want is just 4 columns being filled with hundreds of rows of data, as opposed to hundreds of columns being filled with just a couple of rows of data. Transposing seems to just paste everything under one column.

Here is my code:

Sub ConcatTest()

Dim rCell As Range

For Each rCell In Sheet1.Range("F2", Sheet1.Cells(Sheet1.Rows.Count, "F").End(xlUp))

With Sheets("display").QueryTables.Add(Connection:= _
"URL;https://ws.webtrends.com/v3/Reporting/profiles/17716/reports/oOEWQj3sUo6/?totals=all&start_period=" & rCell & "&measures=1&format=html&suppress_error_codes=true", _
         Destination:=Worksheets("display").Range("A1").End(xlUp)(2, 1))
         

.BackgroundQuery = True

.TablesOnlyFromHTML = True

.Refresh BackgroundQuery:=False

.SaveData = True

End With

Next rCell

End Sub

Can any body see why in this code my data is looping through columns as opposed through rows?

Thanks in advance!