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
"yanf7" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> Hi.
> I have a number of files that have the same structure and include
> couple spreadsheets. I need to make one new file that will copy all
the
> information from the same spreadsheet name "Ratio".
> So at the end I will have a new file with number of spreadsheets
that
> will have names of the files they were copied from.
> Pls help.
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from
http://www.ExcelForum.com/
>
Similar Threads
