Dear Excel Forum,

merge.xlsm

I'm having trouble writing the VB code to open a file name in row A and copy 10 numbers from column A and put them under the file name in the file attached. I'd like to loop through each file name. I have several hundred files to do this to and more than 10 numbers per column but i can edit the VB code.

This is as far as I have got but it is not working.

All help welcome.

Kevin

Sub copyShareData()
Dim path As String, xaxisname As String, rightcount As Single, myfile1 As String, sheetname1 As String
rightcount = 1

i = 1
For i = 1 To 200
rightcount = i + rightcount
path = "c:\users\staff\my documents\shares\"
xaxisname = Cells(1, rightcount).Address
    myfile1 = Range(xaxisname)
    sheetname1 = Left(myfile1, Len(myfile1) - 4)
    Workbooks.Open Filename:=path & myfile1
    ThisWorkbook.Worksheets(1).Range("a1:a10").Formula = "='" & path & "[" & myfile1 & "]" & sheetname1 & "'!$f$1:$f$10"
    Workbooks(myfile1).Close
    Windows("all share data.xlsm").Activate
    Range("a1:a10").Value = Range(Cells(2, 2), Cells(11, 2)).Value
Next i


End Sub