Automatically transfer data from multiple worksheets of workbooks into master workbook

[B00Mbasti]

Hi,
I'm working on a project where i must extract a lot of data out of excel files.
To do that quickly i will use <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym>.
So i used a previously posted code and i changed it:

Sub LoopThroughDirectory()
Dim MyFile As String, Filepath As String, rng As Range, wb As Workbook, i As Long, j As Long
MyFile = Dir("C:\Users\Maarten\Desktop\HTRI-EXCEL\")
Filepath = "C:\Users\Maarten\Desktop\HTRI-EXCEL\"
Do While Len(MyFile) > 0If MyFile = "HTRI-TOT.xlsm" ThenExit SubEnd If
Set wb = Workbooks.Open(Filepath & MyFile)
With wb.Sheets(1)
rngArr = Array("J12", "J13", "G15", "J15", "J18", "P12", "P13", "N15", "P15", "P18", "G20", "G21", "G24", "P20", "P21", "P22", "P23", "P24")
j = 0
For i = LBound(rngArr) To UBound(rngArr)
j = j + 1
.Range(rngArr(i)).Copy Workbooks("HTRI-TOT.xlsm").Sheets(1).Cells(Rows.Count, j).End(xlUp)(2)
Next
End With
wb.Close
MyFile = Dir
Loop
End Sub

This works perfect but this only extract data of one sheet (sheet 1).
My question now is "how to extract data out of more then one sheet (ex. sheet 4) in the same way like this and in one code".


Sorry for my bad English, i hope u understand my question.
My thanks

Maarten

 

[B00Mbasti]

Solved