Hello... I have to write a VBA cod

[amal1973]

Hello...
I have to write a VBA code that will open a Folder, and loop inside of it ,opining every book inside this folder .also copying ten rows from every workbook it opens and pasting it in a main Excel sheet....!!!

I started using a macro to go to the folder and to open it ,but I don't know who to write a loop to fetch every 10 records from every excel sheet that folder contents..

Please help if anyone can
thanks a million

 

[JustinEzequiel]

Was written rather quickly.
Could use a lot more cleaning-up but should get you started.

Sub Test()
Dim cFiles As Collection
Dim sFile As String
Dim sFolder As String
Dim i As Long
Dim oBook As Workbook
Dim nRow As Long
Dim oDest As Worksheet

Set oDest = ActiveWorkbook.ActiveSheet

sFolder = "C:\My Documents"

Set cFiles = New Collection
sFile = Dir(sFolder & "\*.xls&quot
Do While sFile <> &quot;&quot;
cFiles.Add sFolder & &quot;\&quot; & sFile
sFile = Dir
Loop

For i = 1 To cFiles.Count
Set oBook = Application.Workbooks.Open(cFiles(i))
oBook.Sheets(1).Rows(&quot;1:10&quot.Copy
nRow = 1 + (10 * (i - 1))
oDest.Range(&quot;A&quot; & nRow).PasteSpecial xlPasteAll
oBook.Close
Set oBook = Nothing
Next i

Set cFiles = Nothing

Set oDest = Nothing
End Sub