Hi, I'm new to this Forum and VBA for that matter. I was wondering if someone could help me out with what is probably simple question to answer. I have produced a Macro based on the following youtube video:


I have several hundred URLs listed in column A of sheet 1. Here are three typical examples:


At the moment the macro copies data from each of the resulting webpages and pastes it into a new worksheet. See VBA code below. Instead of pasting the data to a new worksheet, what I need the macro to do is paste the data from all the URLs to a specific worksheet, say sheet 2. For example, the data from the 1st URL would be pasted to cells A1:H230, the data from the 2nd would be pasted in the same worksheet to cells A231:H388 and so on. The macro needs to continue extracting and pasting data until there are no URLs remaining (i.e. there is a blank cell).

Please note I have a very basic knowledge of VBA and would prefer the answer to include the revised code and a simple explanation of the changes made.

The current VBA code I’m using is as follows:

Sub Loopthrough()
Dim WSO As Worksheet
Set WSO = ActiveSheet
For Each Cell In WSO.Range("A1:A3")
ThisURL = "URL;" & Cell.Value

With ActiveSheet.QueryTables.Add(Connection:= _
ThisURL, Destination:=Range("$A$1"))
.Name = "tip067.shtml"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

Next Cell
End Sub