PDA

View Full Version : Loop fifty files into one file



inajica
08-11-2009, 06:33 PM
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
08-12-2009, 01:30 AM
Something like



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

inajica
08-12-2009, 08:30 AM
.Cells(.Rows.Count, "A").End(xlUp).Row 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
08-12-2009, 09:58 AM
Sorry, it should have been



Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row