lastrow = sheets("sheet1").cells(rows.count, 3).end(xlup).row
for each c in sheets("sheet1").range("c1:c" & lastrow) ' all cells in column c
nextrow = sheets("sheet2").cells(rows.count, 1)row + 1
With sheets("sheet2").QueryTables.Add(Connection:= _
c.hyperlinks(1), _
Destination:=.Range("$A$" & nextrow))
.Name = mid(c.hyperlinks(1), instrrev(c.hyperlinks(1), "\") + 1)
.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 = "4"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
next