RE: Importing data from multiple closed workbooks

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 work*** 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 Work***
> 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 *** 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.
> >
> >
> >
> >
.