copy cell from file with variable name

[hsg]

I need to copy a cell value from file having name d:\....\file001.xls into
currently
open worksheet in cell E1. The value "001" is a sequence number existing in
current worksheet in cell C1.

The requirement is to copy one cell from file001.xls into E1, file002.xls
into E2 ... and so on. Corresponding numbers exist in C1, C2 .... etc.

i.e. if C10 is "015", then E10 will receive value from file015.xls.
Number in column C are in ascending order.

Is this possible?

 

[Mike H]

Hi,

Right click the sheet tab where you are going to copy the data to, view code
and paste this in and run it.

Sub sonic()
Dim Lastrow As Long
Dim Folder As String, CopyValue As String
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row
Folder = "C:\" 'Change to your path
For Each c In Range("C2:C" & Lastrow)
Workbooks.Open Filename:=Folder & "File" & c.Value & ".xls"
CopyValue = Sheets("Sheet1").Range("A1").Value
ActiveWorkbook.Close False
c.Offset(, 2).Value = CopyValue
Next
End Sub

Mike