my files were BOOK1.xls, BOOK2.xls and so on
so the index started at the 5th character and the number of characters was
the len() of the file name less 8 characters (BOOK + .XLS)

yours will be the same if th efiles all start with the word 'file'


"Patrick Molloy" wrote:

Option Explicit

Sub getdata()

Dim fn As String
Dim wb As Workbook
Dim index As Long

Const filepath As String = "H:\excel\test\"

fn = Dir(filepath & "*.xls")

Do Until fn = ""
index = Mid(fn, 5, Len(fn) - 8)

Set wb = Workbooks.Open(filepath & fn)
ThisWorkbook.ActiveSheet.Cells(index, "B") =
wb.ActiveSheet.Range("B6").Value
wb.Close False
fn = Dir()
Loop


End Sub


"CAUÃ" wrote:

It's possible to write a macro that copy a value that is locate always
in the same position but in different files, this files follow a
nomeclature with logic. And after that paste it in other file.

Example:
In "file1.xls" I want to copy the value in cell "B6" and copy in the
file "all.xls" in cell "B1"
In "file2.xls" I want to copy the value in cell "B6" and copy in the
file "all.xls" in cell "B2"
In "file3.xls"...
And then towards...
Thanks