Hi Greg,

This is great - is there a way to get the array to recognize all files in
the directory rather than have to list out each one?

Krista

"Greg Wilson" wrote:

Oops...

Remove:
wbarr = Array("Combinations.xls", "Combinations.xls", "Combinations.xls",
"Combinations.xls")

I used that for testing purposes. I told you I wrote it in a hurry.

Regards,
Greg

"Greg Wilson" wrote:

Assumed is that the worksheet in Summary.xls is named "Summary" and that
Summary.xls is in the same folder as the others. Change to suit. Note that
this macro doesn't have to open any workbooks or copy and paste and so should
be slicker. Hyperlinks appear to work. I never use them so have no real
experience.

Written in a hurry with minimal testing:

Sub ImportData()
Dim ws As Worksheet
Dim r1 As Range, r2 As Range, c As Range
Dim P As String
Dim wbarr As Variant
Dim i As Integer, ii As Integer

wbarr = Array("Car.xls", "Boat.xls", "Rv.xls", "Loco.xls")
wbarr = Array("Combinations.xls", "Combinations.xls", "Combinations.xls",
"Combinations.xls")
P = ThisWorkbook.Path 'If not correct change to path of above files
Set ws = Sheets("Summary") 'Assumed wks name in "Summary.xls"
Set r1 = ws.Range("A6:E6")
Set r2 = ws.Range("A2:A3, B5:B7") 'Used to get address only

For i = LBound(wbarr) To UBound(wbarr)
ii = 0
For Each c In r2
ii = ii + 1
r1(i + 1, ii).Formula = "= '" & P & _
"\[" & wbarr(i) & "]Sheet1'!" & c.Address
Next c
r1(i + 1, 6).Hyperlinks.Add r1(i + 1, 6), wbarr(i)
Next i

Set r1 = r1.Resize(ii, 5)
r1.Value = r1.Value
End Sub

Regards,
Greg


"Judy" wrote:

I have data in sheets Car.xls; Boat.xls; Rv.xls; Loco.xls
On the first sheet of each of these workbooks, I need to bring in the data
cells a2, a3, b5, b6, b7

And paste it into a summary.xls file.
Starting at Row 6; (a6,b6,c6,d6,e6)
then moving down 1 row for each xls above.

In addition to that, I would like in Column F of each row a hyperlink
created to link back to the source data (Car, Boat, Rv, Loc)

Any and all help is appreciated.