Consolidate Files

[yanf7]

Hi.
I have a number of files that have the same structure and includ
couple spreadsheets. I need to make one new file that will copy all th
information from the same spreadsheet name "Ratio".
So at the end I will have a new file with number of spreadsheets tha
will have names of the files they were copied from.
Pls help

 

[Bernie Deitrick]

yanf,

Put all your files into one folder, then run the macro below after
changing the folder path in the code to match your actual path.

HTH,
Bernie
MS Excel MVP

Sub ConsolidateRatioSheet()
Dim myBook As Workbook
Dim myCalc As XlCalculation

With Application
.EnableEvents = False
.DisplayAlerts = False
myCalc = .Calculation
.Calculation = xlCalculationManual
End With
On Error Resume Next
With Application.FileSearch
.NewSearch
'Change this to your directory
.LookIn = "C:\Excel\Delete These\Ratio"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Set myBook = Workbooks.Open(.FoundFiles(i))
Worksheets("Ratio").Select
Worksheets("Ratio").Name = _
Replace(ActiveWorkbook.Name, ".xls", "")
ActiveWindow.SelectedSheets.Move _
After:=ThisWorkbook.Sheets(1)
myBook.Close False
Next i
Else: MsgBox "There were no files found."
End If
End With
With Application
.EnableEvents = True
.DisplayAlerts = True
.Calculation = myCalc
End With

End Sub