consolidating data from multiple workbooks

[excelusercr]

I currently have to consolidate data from multiple workbooks into a single file. The format of the data is the same and each workbook has the data already selected. Is there a way to automate this using a macro?
I am envisioning the following...
1) Summaryworkbook.summaryWorksheet - this would contain the consolidated data
2) Workbook1.Worksheet1 - worksheet1 would have the data needing to be copied already selected. The macro would need to open this file and then copy the selected data from worksheet1 and paste it to Summaryworkbook.summaryWorksheet, then close this workbook
3) Workbook2.Worksheet2 - worksheet2 would have the data needing to be copied already selected. The macro would need to open this file and then copy the selected data from worksheet1 and paste it to Summaryworkbook.summaryWorksheet, then close this workbook
4) Workbook3.Worksheet3 - worksheet3 would have the data needing to be copied already selected. The macro would need to open this file and then copy the selected data from worksheet1 and paste it to Summaryworkbook.summaryWorksheet, then close this workbook

Any help would be greatly appreciated

 

[sijpie]

Where do you want to have the data copied? On top of each other, below each other, on seperate sheets?

 

[excelusercr]

pasted below

for example...

datarow1
datarow2
(paste new data here - no spaces)

Thanks!

 

[sijpie]

Try this

Option Explicit
Sub consolidate()
    Dim wbSource As Workbook, wbDestiny As Workbook
    Dim lCount As Long
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    
    On Error Resume Next
        Set wbDestiny = ThisWorkbook
        With Application.FileSearch
            .NewSearch
            'Change path to suit
            .LookIn = "C:\MyDocuments\TestResults"
            .FileType = msoFileTypeExcelWorkbooks
            'Optional filter with wildcard
            .Filename = "WorkBook?.xls"
                If .Execute > 0 Then 'Workbooks in folder
                    For lCount = 1 To .FoundFiles.Count 'Loop through all
                        'Open Workbook x and Set a Workbook variable to it
                        Set wbSource = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                        
                        wbSource.ActiveSheet.Selection.Copy
                        wbDestiny.Worksheet("summaryWorksheet").Range("A1").End(xlDown). _
                            Offset(1, 0).PasteSpecial Paste:=xlPasteValues
                        wbSource.Close SaveChanges:=False
                    Next lCount
                End If
        End With
    On Error GoTo 0
    Set wbDestiny = Nothing
    Set wbSource = Nothing
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
End Sub

It will go through all the 'WorkbookX.xls' in the folder specified in the code, and add the selected range.