Loop fifty files into one file

[inajica]

I have fifty files in a folder named TF. Each files are named in numerical order tf1, tf2, tf3 and so on. They already contain data A:e. I need a loop that will open each file and insert corresponding number in column A to the last row of data, and copy all data to a new file. So all fifty files will be one file. thank you.

[Bob Phillips]

Something like

[vba]

Const ROOT_PATH As String = "C:\test\"
Dim wb As wokbook
Dim Lastrow As Long
Dim Nextrow As Long

Set wb = Workbooks.Add
Nextrow = 1
For i = 1 To 50

Workbooks.Open ROOT_PATH & "tf" & i & ".xls"
With ActiveWorkbook.Worksheets(1)

.Cells(.Rows.Count, "A").End(xlUp).Row
.Rows(1).Resize(Lastrow).Copy wb.Worksheets(1).Cells(Nextrow, "A")
Nextrow = Nextrow + 1
End With
ActiveWorkbook.Close savechanges:=False
Next i
[/vba]

[inajica]

[vba] .Cells(.Rows.Count, "A").End(xlUp).Row[/vba] I get yellow block for that line of code when I run the macro, and get an error message Run time error 438 "Object doesn't support this object or method". Thank you for your help

[Bob Phillips]

Sorry, it should have been

[vba]

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
[/vba]