Consolidate data from specific sheets from all workbooks inside of source folder

[ambanares05]

Good Day, I am newbie in excel macro and i need help from someone who is willing to share their expertise in excel macro. My problem is here.

1. I have a folder name "Data Received" this is where i save all of reports i received from my clients and folder location is: "C:\Users\USER\Desktop\Data Received".

2. They are different file types such as .xlsx, .xlsb, .xlsm and .xls.

3. Sample file names are:
SDI Report Week 1_Allan.xls
SDI Report Week 1_Darwin.xlsm
SDI Report Week 1_Crystell.xlsb
SDI Report Week 1_Jhen.xlsx

4. They have lots of sheets inside per file. Each file has sheet name "Summary" and has data on it.

5. My problem is how can i get their summary data and put it in my masterfile or single sheet. Take note that the summary data is formulated so you have also consider to use a copy + paste + special values so i can only get value not the formulated one as i get an error.

This is how i want to describe the process:
Allan(Summary) + Darwin(Summary) + Crystell(Summary) + Jhen(Summary) = paste into one excel sheet (Masterfile) or you can call it (Consolidated) something like that.

Just copy and paste value function as it is formulated cells. I get error if you just copy and paste it in masterfile as they are missing link/data.


I hope anyone could help me solve my problem. Thank you so much guys in advance.

 

[mumps]

Try:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Const strPath As String = "C:\Users\USER\Desktop\Data Received\"
    ChDir strPath
    strExtension = Dir(strPath & "*.xls*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
            .Sheets("Summary").UsedRange.Copy
            wkbDest.Sheets("Consolidated").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

Destination sheet is named "Consolidated".

 

[ambanares05]

Hi mumps,

The code gives the exact results that i want. I've tried to run the code and put a 10 sample files inside "Data Received" folder. During the run time, there was a message prompt in my screen says "There is a large amount of information on the clipboard. Do you want to be able to paste this information into another program later?" something like that. Is there a way to bypass it? If not it's okay, i'll press it one-by-one.

I'd like to thank you for quick response to my concern and i was amazed by result because you gave exact results that i need.

 

[winrow]

mumps might have another opinion but try

[COLOR=#333333][FONT=Menlo]Application.DisplayAlerts = False[/FONT][/COLOR]

under the other one at the beginning and at the end reactivate

[COLOR=#333333][FONT=Menlo]Application.DisplayAlerts = True[/FONT][/COLOR]

 

[mumps]

You are very welcome. winrow's suggestion should work. Just place all the code between the 2 lines he suggested.