VBA: How to get multiple web queries onto one sheet VERTICALLY

[bennyb]

Hey folks,
I've written a macro to pull info from different web pages. It all works just like I want it to except it puts the information onto my worksheet horizontally. The query imports a table (the 10th on the page) for each item, and each table is 3 rows long.
I've got the macro searching every item in column D on the sheet "Sorted" and I'd like to have the results one on top of the other vertically, starting at Cell C2 of the sheet "Sheet375". Here's my code so far (I've replaced my URL with ...):

Option Explicit
Sub TryThis()
Dim cl As Range, rng As Range
Dim strName As String
Dim myRows As Long
Set rng = Worksheets("Sorted").Range("D:D")
Application.ScreenUpdating = False

For Each cl In rng
If Trim(cl.Value) <> "" Then
strName = cl.Value
myRows = Sheets("Sheet375").Cells(Rows.Count, "C").End(xlUp).Row + 3
Sheets("Sheet375").Activate


With ActiveSheet.QueryTables.Add(Connection:="..."& strName, Destination:=Range("C2"))
.Name = "q?s=" & strName
.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 = "10"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End If
Next cl
End Sub


Thanks in advance

 

[bennyb]

SOLVED Re: VBA: How to get multiple web queries onto one sheet VERTICALLY

Figured this one out myself. It was a simple fix. Here's the code:
Option Explicit
Sub TryThis()
Dim cl As Range, rng As Range
Dim strName As String
Dim myRows As Long
Set rng = Worksheets("Sorted").Range("D:D")
Application.ScreenUpdating = False

For Each cl In rng
If Trim(cl.Value) <> "" Then
strName = cl.Value
myRows = Sheets("Sheet375").Cells(Rows.Count, "C").End(xlUp).Row + 3
Sheets("Sheet375").Activate


With ActiveSheet.QueryTables.Add(Connection:="..."& strName, Destination:=Range("C" &myRows))
.Name = "q?s=" & strName
.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 = "10"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End If
Next cl
End Sub