Consolidate lots of files

Hello everyone:

Am brand new in vba and Macros with Excel, and I've been working to improve some manual tasks performed every Month…

The main issue is that monthly are generated between 100 and 3000 excel files, all with the same estructure and characteristics (the data is in the same cells and range of cells), I have been able to reach the files using the ThisWorkbook.path & "\reports\" method but I don't know how to get the data stored in the sheet "REPORT" from the 3 visible sheets and 7 hidden sheets.

The data that I need to get is in Cells, A8 (but only the last number not the text in the beginning), A11 and in the range (that can change from 1 row to 300 rows, only need the rows with data), B30 (Description), C30 (Quantity), and D30 (Units).

The final result have to be like this


result.png

According to your attachment a VBA demonstration for starters (v2) :

PHP Code: 

Sub Demo1()
    Dim P$, F$, V(), W(), L&, R&, C%
        P = ThisWorkbook.Path & "\cotizaciones\"
        F = Dir$(P & "*.xlsx"):  If F = "" Then Beep: Exit Sub
        [A1].CurrentRegion.Offset(1).Clear
        ReDim V(1 To Rows.Count - 1, 1 To 5)
    With CreateObject("ADODB.Connection")
        Do
               .Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=""Excel 12.0;HDR=No"";Data Source=" & P & F
                W = .Execute("[COTIZACION$A8:A21]").GetRows
                V(R + 1, 1) = Mid(W(0, 0), InStrRev(W(0, 0), " ") + 1):  V(R + 1, 2) = W(0, 13)
                W = .Execute("[COTIZACION$B30:D48]").GetRows:  L = 0
               .Close
            Do
                R = R + 1:  For C = 3 To 5:  V(R, C) = W(C - 3, L):  Next:   L = L + 1
            Loop Until IsNull(W(0, L))
                   F = Dir$
        Loop Until F = ""
    End With
        [A2].Resize(R, 5).Value2 = V
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄