sachin30us
New Member
- Joined
- Feb 8, 2012
- Messages
- 14
Dim LoanNo As New Collection
Dim Balance As New Collection
Dim BegBalance As New Collection
Dim SchPrinc As New Collection
Dim Prepay As New Collection
Dim Liq As New Collection
Dim Loss As New Collection
Dim AddLoss As New Collection
Dim PayOffdes As New Collection
Dim PayOffDate As New Collection
Dim AddLoss1 As New Collection
Dim LoanRate As New Collection
Dim LossSev As New Collection
Dim PrepayPen As New Collection
Dim State As New Collection
Dim Lien As New Collection
Dim SecurityName As New Collection
Dim FileName As New Collection
' Prevents screen refreshing.
Application.ScreenUpdating = False
' Disable automatic calculation
Application.Calculation = xlCalculationManual
Folder = ActiveWorkbook.Path & "\minis"
Files = Dir$((Folder & "\*.xls"), vbNormal)
Do While Len(Files)
FileName.Add Files
Files = Dir
Loop
Range("A5:Q500").Select
Selection.ClearContents
SecurityName.Add "2007-131"
SecurityName.Add "2007-132"
SecurityName.Add "2007-133"
SecurityName.Add "2007-134"
SecurityName.Add "2007-15"
SecurityName.Add "2007-23"
SecurityName.Add "2007-30"
SecurityName.Add "2007-31"
SecurityName.Add "2007-32"
SecurityName.Add "2007-QH1"
jj = 4
For k = 1 To FileName.Count
Set wbook = Workbooks.Open(Folder & "\" & FileName(k))
SheetExists = 0
For iSht = 1 To wbook.Sheets.Count
If (wbook.Sheets(iSht).Name = "Prepayment & Liquidation Loan D") Then
SheetExists = 1
End If
Next
If (SheetExists) Then
For i = 21 To 500
If (IsNumeric(wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 2).Value) And wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 2).Value <> "") Then
LoanNo.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 2).Value
Balance.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 5).Value
BegBalance.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 7).Value
SchPrinc.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 10).Value
Prepay.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 11).Value
Liq.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 14).Value
Loss.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 15).Value
AddLoss.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 19).Value
PayOffdes.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 22).Value
PayOffDate.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 25).Value
AddLoss1.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 27).Value
LoanRate.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 29).Value / 100
LossSev.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 32).Value / 100
PrepayPen.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 35).Value
State.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 38).Value
Lien.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 40).Value
End If
Next i
Workbooks("Mini_Loan_Extract.xlsm").Activate
For j = 1 To LoanNo.Count
jj = jj + 1
Worksheets("Main").Cells(jj, 1).Value = SecurityName(k)
Worksheets("Main").Cells(jj, 2).Value = LoanNo(j)
Worksheets("Main").Cells(jj, 3).Value = Balance(j)
Worksheets("Main").Cells(jj, 4).Value = BegBalance(j)
Worksheets("Main").Cells(jj, 5).Value = SchPrinc(j)
Worksheets("Main").Cells(jj, 6).Value = Prepay(j)
Worksheets("Main").Cells(jj, 7).Value = Liq(j)
Worksheets("Main").Cells(jj, 8).Value = Loss(j)
Worksheets("Main").Cells(jj, 9).Value = AddLoss(j)
Worksheets("Main").Cells(jj, 10).Value = PayOffdes(j)
Worksheets("Main").Cells(jj, 11).Value = PayOffDate(j)
Worksheets("Main").Cells(jj, 12).Value = AddLoss1(j)
Worksheets("Main").Cells(jj, 13).Value = LoanRate(j)
Worksheets("Main").Cells(jj, 14).Value = LossSev(j)
Worksheets("Main").Cells(jj, 15).Value = PrepayPen(j)
Worksheets("Main").Cells(jj, 16).Value = State(j)
Worksheets("Main").Cells(jj, 17).Value = Lien(j)
Next
End If
wbook.Close
Set LoanNo = Nothing
Set Balance = Nothing
Set BegBalance = Nothing
Set SchPrinc = Nothing
Set Prepay = Nothing
Set Liq = Nothing
Set Loss = Nothing
Set AddLoss = Nothing
Set PayOffdes = Nothing
Set PayOffDate = Nothing
Set AddLoss1 = Nothing
Set LoanRate = Nothing
Set LossSev = Nothing
Set PrepayPen = Nothing
Set State = Nothing
Set Lien = Nothing
Next k
ActiveWorkbook.Worksheets("Main").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Main").Sort.SortFields.Add Key:=Range("J5:J500") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Main").Sort.SortFields.Add Key:=Range("K5:K500") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Main").Sort
.SetRange Range("A4:Q500")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' Turn automatic calculations back on
Application.Calculation = xlCalculationAutomatic
' Enables screen refreshing.
Application.ScreenUpdating = True
End Sub
The above macro goes into 2007-134 excel file in a folder mini and only copies that data......... in the existing file.. but it looks and collects data from other files like 2007-131 under mini folder but dosent go on every file to spit out the data into the main file.. can someone help?
Dim Balance As New Collection
Dim BegBalance As New Collection
Dim SchPrinc As New Collection
Dim Prepay As New Collection
Dim Liq As New Collection
Dim Loss As New Collection
Dim AddLoss As New Collection
Dim PayOffdes As New Collection
Dim PayOffDate As New Collection
Dim AddLoss1 As New Collection
Dim LoanRate As New Collection
Dim LossSev As New Collection
Dim PrepayPen As New Collection
Dim State As New Collection
Dim Lien As New Collection
Dim SecurityName As New Collection
Dim FileName As New Collection
' Prevents screen refreshing.
Application.ScreenUpdating = False
' Disable automatic calculation
Application.Calculation = xlCalculationManual
Folder = ActiveWorkbook.Path & "\minis"
Files = Dir$((Folder & "\*.xls"), vbNormal)
Do While Len(Files)
FileName.Add Files
Files = Dir
Loop
Range("A5:Q500").Select
Selection.ClearContents
SecurityName.Add "2007-131"
SecurityName.Add "2007-132"
SecurityName.Add "2007-133"
SecurityName.Add "2007-134"
SecurityName.Add "2007-15"
SecurityName.Add "2007-23"
SecurityName.Add "2007-30"
SecurityName.Add "2007-31"
SecurityName.Add "2007-32"
SecurityName.Add "2007-QH1"
jj = 4
For k = 1 To FileName.Count
Set wbook = Workbooks.Open(Folder & "\" & FileName(k))
SheetExists = 0
For iSht = 1 To wbook.Sheets.Count
If (wbook.Sheets(iSht).Name = "Prepayment & Liquidation Loan D") Then
SheetExists = 1
End If
Next
If (SheetExists) Then
For i = 21 To 500
If (IsNumeric(wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 2).Value) And wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 2).Value <> "") Then
LoanNo.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 2).Value
Balance.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 5).Value
BegBalance.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 7).Value
SchPrinc.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 10).Value
Prepay.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 11).Value
Liq.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 14).Value
Loss.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 15).Value
AddLoss.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 19).Value
PayOffdes.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 22).Value
PayOffDate.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 25).Value
AddLoss1.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 27).Value
LoanRate.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 29).Value / 100
LossSev.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 32).Value / 100
PrepayPen.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 35).Value
State.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 38).Value
Lien.Add wbook.Sheets("Prepayment & Liquidation Loan D").Cells(i, 40).Value
End If
Next i
Workbooks("Mini_Loan_Extract.xlsm").Activate
For j = 1 To LoanNo.Count
jj = jj + 1
Worksheets("Main").Cells(jj, 1).Value = SecurityName(k)
Worksheets("Main").Cells(jj, 2).Value = LoanNo(j)
Worksheets("Main").Cells(jj, 3).Value = Balance(j)
Worksheets("Main").Cells(jj, 4).Value = BegBalance(j)
Worksheets("Main").Cells(jj, 5).Value = SchPrinc(j)
Worksheets("Main").Cells(jj, 6).Value = Prepay(j)
Worksheets("Main").Cells(jj, 7).Value = Liq(j)
Worksheets("Main").Cells(jj, 8).Value = Loss(j)
Worksheets("Main").Cells(jj, 9).Value = AddLoss(j)
Worksheets("Main").Cells(jj, 10).Value = PayOffdes(j)
Worksheets("Main").Cells(jj, 11).Value = PayOffDate(j)
Worksheets("Main").Cells(jj, 12).Value = AddLoss1(j)
Worksheets("Main").Cells(jj, 13).Value = LoanRate(j)
Worksheets("Main").Cells(jj, 14).Value = LossSev(j)
Worksheets("Main").Cells(jj, 15).Value = PrepayPen(j)
Worksheets("Main").Cells(jj, 16).Value = State(j)
Worksheets("Main").Cells(jj, 17).Value = Lien(j)
Next
End If
wbook.Close
Set LoanNo = Nothing
Set Balance = Nothing
Set BegBalance = Nothing
Set SchPrinc = Nothing
Set Prepay = Nothing
Set Liq = Nothing
Set Loss = Nothing
Set AddLoss = Nothing
Set PayOffdes = Nothing
Set PayOffDate = Nothing
Set AddLoss1 = Nothing
Set LoanRate = Nothing
Set LossSev = Nothing
Set PrepayPen = Nothing
Set State = Nothing
Set Lien = Nothing
Next k
ActiveWorkbook.Worksheets("Main").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Main").Sort.SortFields.Add Key:=Range("J5:J500") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Main").Sort.SortFields.Add Key:=Range("K5:K500") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Main").Sort
.SetRange Range("A4:Q500")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' Turn automatic calculations back on
Application.Calculation = xlCalculationAutomatic
' Enables screen refreshing.
Application.ScreenUpdating = True
End Sub
The above macro goes into 2007-134 excel file in a folder mini and only copies that data......... in the existing file.. but it looks and collects data from other files like 2007-131 under mini folder but dosent go on every file to spit out the data into the main file.. can someone help?