Hi, I have a code that lists some hyperlinks in column O to other xls documents with data that I need to pull. The data is found on a page called 'Quote' in cells B7, B8, B11 and B13. I need these cells copied and pasted into rows from A2 to D2 and following down for each hyperlink.
What I'm looking for may be along the same lines as this code but just filling in the gaps as seen. I can't really individually select the links as it is a very very long list
Thanks in advance
What I'm looking for may be along the same lines as this code but just filling in the gaps as seen. I can't really individually select the links as it is a very very long list
Code:
Sub GatherData()
Dim wbTarget As Workbook
Dim ary(4) As Variant
Dim lRow As Long
Dim CodeNames As Variant, i As Long
CodeNames = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
'For Each hyperlink in the list
For i = 1 To UBound(CodeNames, 1)
If InStr(1, cel.Value, ".xls") > 0 Then
Set wbTarget = Workbooks.Open(The hyperlinked workbook)
With wbTarget.Worksheets("Quote")
ary(0) = .Range("B7")
ary(1) = .Range("B8")
ary(2) = .Range("B11")
ary(3) = .Range("B13")
End With
With wbMaster.Worksheets(1)
lRow = .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).Row
.Range("A" & lRow & ":D" & lRow) = ary
End With
wbTarget.Close SaveChanges:=False
End If
'close the opened link
'Next link
End Sub
Thanks in advance
Last edited: