Extracting data from multiple Excel files

[atgjerts]

Hi!

I'm new in this forum and a novice when it comes to visual basic. I found this code on the web, and adapted it to my problem. The challenge is to extract data from specific cells in hundreds of identical Excel files into one excel spraedsheet.

I get two different error messages:

Runtime error 1004
Application-defined or object defined error,

and

Runtime error 9
"subscript out of range"

The code is as follows (the error is in the marked line):

Sub Hente_data()
Dim myFile As String, myCurrFile As String
myCurrFile = ThisWorkbook.Name
myFile = Dir("F:\internt\fakturert\2008\*.xls")
Do Until myFile = ""
Workbooks.Open "F:\internt\fakturert\2008\" & myFile
Workbooks(myCurrFile).Worksheets("Data").Range("a1").End(xlDown).Offset(1, 0) = Workbooks(myFile).Worksheets("skjema").Range("j10")
Workbooks(myFile).Close savechanges:=False
myFile = Dir
Loop

End Sub

Anyone who can help?

[atgjerts]

This problem is now solved. The problem was that xldown in combination with offset1, 0), tried to put a value in one cell further down than the maximum range.

The solution was to put Range (a1500).end(xlup).Offset(1,0).

This did the trick....