Data extraction from multiple workbook

[MrJ1m]

Hi everyone,
I am still a big newbie when it comes to writting in vba. I still make a lot of mistakes, but it is getting better with the practice, and the help i got online and from some buddies.
Anyway, I have a folder containing 5 workbook (5 CAC40 stocks), each containing between 2 and 3 sheets, which are full of trading information. Each sheets contain minimum 400k to 500k rows, the biggest going around 900k.
The datas are organized like this in every sheets:
ID DATE TIME PRICE QUANTITY filledorders

I need to extract the first two trade of the day, the fixing, and the one that occured immediatly after.
So i worked my way through, with some help, to come up with a macro working for one workbook. But now that I am trying to loop through 5 workbook, it does not work. Here is my code:

Sub MACRO2BATAR()

Dim lngFirstRow As Long, lngLastRow As Long, cRow As Long, lngNextDestRow As Long, i As Integer
Dim shSrc As Worksheet, shDest As Worksheet
Dim Wb As Workbook
Dim WbName(1 To 5) As String
Dim intAppCalc As Integer 'added variable to store original calculation setting


Application.ScreenUpdating = False
Application.EnableEvents = False
intAppCalc = Application.Calculation 'store original calculation setting
Application.Calculation = xlCalculationManual


WbName(1) = "CARREFOUR"
WbName(2) = "EDF"
WbName(3) = "SOCGEN"
WbName(4) = "TOTAL"
WbName(5) = "SANOFI"


lngNextDestRow = 2


For i = 1 To 5


'changed the workbook references
ThisWorkbook.Worksheets.Add
ThisWorkbook.ActiveSheet.Name = WbName(i)
Set shDest = ThisWorkbook.ActiveSheet  '''Feuille de destination (sheetDestination)


Workbooks.Open ("Users:uknowwho:Desktop:ProjetVBA:" & WbName(i) & ".xlsx")


    For Each shSrc In ActiveWorkbook.Worksheets 'changed ThisWorkbook to ActiveWorkbook


            With shSrc
                'added condition to check if there is data in column "B"
                If Not .Columns(2).Find(What:="*", LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False) Is Nothing Then
                    lngFirstRow = 2
                    lngLastRow = .Columns(2).Find(What:="*", LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row


                    For cRow = lngFirstRow To lngLastRow
                            If .Cells(cRow, 2) <> .Cells(cRow - 1, 2) Then
                                .Range("B" & cRow).Copy Destination:=shDest.Range("A" & lngNextDestRow)
                                .Range("D" & cRow).Copy Destination:=shDest.Range("B" & lngNextDestRow)
                                .Range("D" & cRow + 1).Copy Destination:=shDest.Range("C" & lngNextDestRow)
                                .Range("E" & cRow).Copy Destination:=shDest.Range("D" & lngNextDestRow)
                                .Range("E" & cRow + 1).Copy Destination:=shDest.Range("E" & lngNextDestRow)
                                .Range("F" & cRow).Copy Destination:=shDest.Range("F" & lngNextDestRow)
                                .Range("F" & cRow + 1).Copy Destination:=shDest.Range("G" & lngNextDestRow)
                                lngNextDestRow = lngNextDestRow + 1
                            End If
                    Next cRow
                End If
            End With


     Next shSrc


     Workbooks(WbName(i) & ".xlsx").Close
Next i


Application.Calculation = intAppCalc 'restore original calculation setting
Application.EnableEvents = False
Application.ScreenUpdating = False


End Sub

It extract the data properly for i=1, but for the rest of the loop, it only create the sheets in the new workbook, and do not extract any data from corresponding workbook...

Any help would be great, cause i feel i am close
Thanks in advance!

 

[MrJ1m]

So, i found that it was working fine all along.
But i need to reset the variable lngNextDestRow i think as when i changes, the datas extracted from the second workbook start around line 392 in the new sheet, and 784 in the following etc.
I'll post the updated code and updated the title