Hi! I have 160 excel files and i'm writing a macro to help me copy some cells from each file and paste them to file called Sample.xls. I have a loop that enables me to run the macro for all the 160 files in one folder but i want the information from each file to be in one row and then the information from another file to be in the next row, and so on. My is the following: Sub lol() Dim sFile As String Dim i As Integer Dim A As Range Const sPath As String = "C:teste\" Iint = 0 sFile = Dir(sPath & "*.*") Do While sFile <> "" And sFile <> "Book1.xls" And sFile <> "Book3.xlsx" MsgBox sFile Workbooks.Open (sPath & sFile) Columns("C:C").Select Selection.Insert Shift:=xlToRight Range("C29").Select ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""EMPTY"",1,0)" Range("C29").Select Selection.AutoFill Destination:=Range("C29:C24220"), Type:=xlFillDefault Range("C29:C24220").Select Range("C24221").Select ActiveCell.FormulaR1C1 = "=SUM(R[-24192]C:R[-1]C)" Range("C24221").Select Selection.Copy Workbooks.Open Filename:="C:teste\Book1.xls" If Range("A" & Iint + 1).Value = "" Then Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Windows(sFile).Activate Range("A1").Select Selection.Copy Windows("Book1.xls").Activate ActiveCell = ActiveCell.Offset(0, 1) ActiveSheet.Paste ActiveWorkbook.Save ActiveWorkbook.Close savechanges:=True Windows(sFile).Activate ActiveWorkbook.Save ActiveWorkbook.Close savechanges:=True End If sFile = Dir Loop End Sub I don't know how to do that. If you could help me i would really apreciated. Thanks in advance
"ana saomarcos" wrote: > Hi! > > I have 160 excel files and i'm writing a macro to help me copy some cells from each file and paste them to file called Sample.xls. You should post this in the "excel.programming" group. By the way, Const sPath As String = "C:teste\" should be Const sPath As String = "C:\teste\"
