CODE help - Copy data from closed workbook

[staticbob]

Hi Guys,

I have 7 workbooks I would like to copy data from. The data in question is a sheet called "Accident Book" in each of the 7 workbooks.

I know how to copy the data, but how do I reference these 7 closed workbooks ? They are all in the same directory, but these are other files in there that I dont need, so I dont just want to point to the directory.

I have this code from Nimrod but can't get it working.


Sub GetValuesFromAClosedWorkbook(fPath As String, fName As String, sName, cellRange As String)
With ActiveSheet.Range("A65536").End(xlUp)
.FormulaArray = "='" & "\\Bls36dc10\Work\H&S" & "\[" & Central 2004.xls & "]" _
& "Accident Book" & "'!" & range("A6")
.Value = .Value
End With
End Sub


I actually want to copy data from A6:N6 down to the last row entry, then paste this into the next available row in the activesheet. I need to do this from 7 different workbooks.

Cheers.

Bob


Bob

 

[outlook]

Can anyone help me ... I have been looking at this thread from years ago, and wondered if this code can be adjusted as at the moment is copying all data from my four sheets and adding them from row A8 onwards.

It should copy data from all four sheets (from row A8 onwards) to my master sheet (from A8 onwards.)

Help!!!