how to copy cells from excel files and paste it toanother?

[ana saomarcos]

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

 

[Jean-Guy Marcil]

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\"