API tools faq
paste
Advertisement
VRonin

Untitled

VRonin
Aug 20th, 2013
144
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
VisualBasic 16.78 KB | None | 0 0
raw download clone embed print report
  1. Option Explicit
  2. Const TargetSheetName = "New Data Consolidation"
  3. Const MonthsBackInTime = 12
  4. Const StartingSheet = 5
  5. Const EndingSheet = 22
  6. Public Sub PrepareConsolidation()
  7. '---------------------------------------------------------------------------------------
  8. ' Procedure : PrepareConsolidation
  9. ' Author    : L.B.
  10. ' Date      : 20/08/2013
  11. ' Purpose   : Consolidate the main data from the single mastertrusts
  12. '---------------------------------------------------------------------------------------
  13.  
  14.     Application.ScreenUpdating = False
  15.     Dim FieldNumber As Integer
  16.     Dim FieldTag As String
  17.     Dim i As Long, j As Long
  18.     Dim FieldRow As Long
  19.     For i = 1 To MonthsBackInTime
  20.         With Sheets(TargetSheetName).Range("A1").Offset(0, i)
  21.             .Value = Application.WorksheetFunction.EoMonth(Date, -(i + 1)) + 1
  22.             .NumberFormat = "mmm-yy"
  23.         End With
  24.     Next i
  25. '---------------------------------------------------------------------------------------
  26. ' 90+ Arrears
  27. '---------------------------------------------------------------------------------------
  28.    FieldNumber = 0
  29.     FieldTag = "3m"
  30.     Sheets(TargetSheetName).Range("A2").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Value = "90+ Arrears"
  31.     Sheets(TargetSheetName).Range("A2").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Font.Italic = True
  32.     On Error Resume Next
  33.     For i = StartingSheet To EndingSheet
  34.         FieldRow = -1
  35.         FieldRow = Sheets(i).Range("B:B").Find(What:=FieldTag).Row
  36.         With Sheets(TargetSheetName).Range("A3").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Offset(i - StartingSheet, 0)
  37.             .Value = Sheets(i).Name
  38.             If (FieldRow > 0) Then
  39.                 For j = 1 To MonthsBackInTime
  40.                     .Offset(0, j).Value = Sheets(i).Range("A1").Offset(FieldRow - 1, _
  41.                         Sheets(i).Range("A1:XFD1048576").Find(What:=Sheets(TargetSheetName).Range("A1").Offset(0, j).Value).Column - 1).Value
  42.                 Next j
  43.             End If
  44.         End With
  45.     Next i
  46.     On Error GoTo 0
  47.     With Sheets(TargetSheetName).Range("A3").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Resize(EndingSheet - StartingSheet + 1, MonthsBackInTime + 1)
  48.         .NumberFormat = "0.00%"
  49.         .Borders.LineStyle = xlContinuous
  50.         .Borders.Weight = xlThin
  51.         .Borders.ColorIndex = xlAutomatic
  52.         .Borders(xlEdgeTop).Weight = xlMedium
  53.         .Borders(xlEdgeBottom).Weight = xlMedium
  54.         .Borders(xlEdgeRight).Weight = xlMedium
  55.         .Borders(xlEdgeLeft).Weight = xlMedium
  56.         .Interior.Color = RGB(118, 147, 60)
  57.     End With
  58. '---------------------------------------------------------------------------------------
  59. ' CPR
  60. '---------------------------------------------------------------------------------------
  61.    FieldNumber = FieldNumber + 1
  62.     FieldTag = "cpr"
  63.     Sheets(TargetSheetName).Range("A2").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Value = "CPR"
  64.     Sheets(TargetSheetName).Range("A2").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Font.Italic = True
  65.     On Error Resume Next
  66.     For i = StartingSheet To EndingSheet
  67.         FieldRow = -1
  68.         FieldRow = Sheets(i).Range("B:B").Find(What:=FieldTag).Row
  69.         With Sheets(TargetSheetName).Range("A3").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Offset(i - StartingSheet, 0)
  70.             .Value = Sheets(i).Name
  71.             If (FieldRow > 0) Then
  72.                 For j = 1 To MonthsBackInTime
  73.                     .Offset(0, j).Value = Sheets(i).Range("A1").Offset(FieldRow - 1, _
  74.                         Sheets(i).Range("A1:XFD1048576").Find(What:=Sheets(TargetSheetName).Range("A1").Offset(0, j).Value).Column - 1).Value
  75.                 Next j
  76.             End If
  77.         End With
  78.     Next i
  79.     On Error GoTo 0
  80.     With Sheets(TargetSheetName).Range("A3").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Resize(EndingSheet - StartingSheet + 1, MonthsBackInTime + 1)
  81.         .NumberFormat = "0.00%"
  82.         .Borders.LineStyle = xlContinuous
  83.         .Borders.Weight = xlThin
  84.         .Borders.ColorIndex = xlAutomatic
  85.         .Borders(xlEdgeTop).Weight = xlMedium
  86.         .Borders(xlEdgeBottom).Weight = xlMedium
  87.         .Borders(xlEdgeRight).Weight = xlMedium
  88.         .Borders(xlEdgeLeft).Weight = xlMedium
  89.         .Interior.Color = RGB(118, 147, 60)
  90.     End With
  91. '---------------------------------------------------------------------------------------
  92. ' AAA CE
  93. '---------------------------------------------------------------------------------------
  94.    FieldNumber = FieldNumber + 1
  95.     FieldTag = "aaa"
  96.     Sheets(TargetSheetName).Range("A2").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Value = "Most senior CE"
  97.     Sheets(TargetSheetName).Range("A2").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Font.Italic = True
  98.     On Error Resume Next
  99.     For i = StartingSheet To EndingSheet
  100.         FieldRow = -1
  101.         FieldRow = Sheets(i).Range("B:B").Find(What:=FieldTag).Row
  102.         With Sheets(TargetSheetName).Range("A3").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Offset(i - StartingSheet, 0)
  103.             .Value = Sheets(i).Name
  104.             If (FieldRow > 0) Then
  105.                 For j = 1 To MonthsBackInTime
  106.                     .Offset(0, j).Value = Sheets(i).Range("A1").Offset(FieldRow - 1, _
  107.                         Sheets(i).Range("A1:XFD1048576").Find(What:=Sheets(TargetSheetName).Range("A1").Offset(0, j).Value).Column - 1).Value
  108.                 Next j
  109.             End If
  110.         End With
  111.     Next i
  112.     On Error GoTo 0
  113.     With Sheets(TargetSheetName).Range("A3").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Resize(EndingSheet - StartingSheet + 1, MonthsBackInTime + 1)
  114.         .NumberFormat = "0.00%"
  115.         .Borders.LineStyle = xlContinuous
  116.         .Borders.Weight = xlThin
  117.         .Borders.ColorIndex = xlAutomatic
  118.         .Borders(xlEdgeTop).Weight = xlMedium
  119.         .Borders(xlEdgeBottom).Weight = xlMedium
  120.         .Borders(xlEdgeRight).Weight = xlMedium
  121.         .Borders(xlEdgeLeft).Weight = xlMedium
  122.         .Interior.Color = RGB(118, 147, 60)
  123.     End With
  124. '---------------------------------------------------------------------------------------
  125. ' Reserve Fund
  126. '---------------------------------------------------------------------------------------
  127.    FieldNumber = FieldNumber + 1
  128.     FieldTag = "res"
  129.     Sheets(TargetSheetName).Range("A2").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Value = "Reserve Fund"
  130.     Sheets(TargetSheetName).Range("A2").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Font.Italic = True
  131.     On Error Resume Next
  132.     For i = StartingSheet To EndingSheet
  133.         FieldRow = -1
  134.         FieldRow = Sheets(i).Range("B:B").Find(What:=FieldTag).Row
  135.         With Sheets(TargetSheetName).Range("A3").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Offset(i - StartingSheet, 0)
  136.             .Value = Sheets(i).Name
  137.             If (FieldRow > 0) Then
  138.                 For j = 1 To MonthsBackInTime
  139.                     .Offset(0, j).Value = Sheets(i).Range("A1").Offset(FieldRow - 1, _
  140.                         Sheets(i).Range("A1:XFD1048576").Find(What:=Sheets(TargetSheetName).Range("A1").Offset(0, j).Value).Column - 1).Value
  141.                 Next j
  142.             End If
  143.         End With
  144.     Next i
  145.     On Error GoTo 0
  146.     With Sheets(TargetSheetName).Range("A3").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Resize(EndingSheet - StartingSheet + 1, MonthsBackInTime + 1)
  147.         .NumberFormat = "0.00%"
  148.         .Borders.LineStyle = xlContinuous
  149.         .Borders.Weight = xlThin
  150.         .Borders.ColorIndex = xlAutomatic
  151.         .Borders(xlEdgeTop).Weight = xlMedium
  152.         .Borders(xlEdgeBottom).Weight = xlMedium
  153.         .Borders(xlEdgeRight).Weight = xlMedium
  154.         .Borders(xlEdgeLeft).Weight = xlMedium
  155.         .Interior.Color = RGB(118, 147, 60)
  156.     End With
  157. '---------------------------------------------------------------------------------------
  158. ' Excess Spread
  159. '---------------------------------------------------------------------------------------
  160.    FieldNumber = FieldNumber + 1
  161.     FieldTag = "excess"
  162.     Sheets(TargetSheetName).Range("A2").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Value = "Excess Spread"
  163.     Sheets(TargetSheetName).Range("A2").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Font.Italic = True
  164.     On Error Resume Next
  165.     For i = StartingSheet To EndingSheet
  166.         FieldRow = -1
  167.         FieldRow = Sheets(i).Range("B:B").Find(What:=FieldTag).Row
  168.         With Sheets(TargetSheetName).Range("A3").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Offset(i - StartingSheet, 0)
  169.             .Value = Sheets(i).Name
  170.             If (FieldRow > 0) Then
  171.                 For j = 1 To MonthsBackInTime
  172.                     .Offset(0, j).Value = Sheets(i).Range("A1").Offset(FieldRow - 1, _
  173.                         Sheets(i).Range("A1:XFD1048576").Find(What:=Sheets(TargetSheetName).Range("A1").Offset(0, j).Value).Column - 1).Value
  174.                 Next j
  175.             End If
  176.         End With
  177.     Next i
  178.     On Error GoTo 0
  179.     With Sheets(TargetSheetName).Range("A3").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Resize(EndingSheet - StartingSheet + 1, MonthsBackInTime + 1)
  180.         .NumberFormat = "0.00%"
  181.         .Borders.LineStyle = xlContinuous
  182.         .Borders.Weight = xlThin
  183.         .Borders.ColorIndex = xlAutomatic
  184.         .Borders(xlEdgeTop).Weight = xlMedium
  185.         .Borders(xlEdgeBottom).Weight = xlMedium
  186.         .Borders(xlEdgeRight).Weight = xlMedium
  187.         .Borders(xlEdgeLeft).Weight = xlMedium
  188.         .Interior.Color = RGB(118, 147, 60)
  189.     End With
  190. '---------------------------------------------------------------------------------------
  191. ' Size
  192. '---------------------------------------------------------------------------------------
  193.    FieldNumber = FieldNumber + 1
  194.     FieldTag = "size"
  195.     Sheets(TargetSheetName).Range("A2").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Value = "Mortgage Amount outstanding"
  196.     Sheets(TargetSheetName).Range("A2").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Font.Italic = True
  197.     On Error Resume Next
  198.     For i = StartingSheet To EndingSheet
  199.         FieldRow = -1
  200.         FieldRow = Sheets(i).Range("B:B").Find(What:=FieldTag).Row
  201.         With Sheets(TargetSheetName).Range("A3").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Offset(i - StartingSheet, 0)
  202.             .Value = Sheets(i).Name
  203.             If (FieldRow > 0) Then
  204.                 For j = 1 To MonthsBackInTime
  205.                     .Offset(0, j).Value = Sheets(i).Range("A1").Offset(FieldRow - 1, _
  206.                         Sheets(i).Range("A1:XFD1048576").Find(What:=Sheets(TargetSheetName).Range("A1").Offset(0, j).Value).Column - 1).Value
  207.                 Next j
  208.             End If
  209.         End With
  210.     Next i
  211.     On Error GoTo 0
  212.     With Sheets(TargetSheetName).Range("A3").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Resize(EndingSheet - StartingSheet + 1, MonthsBackInTime + 1)
  213.         .NumberFormat = "#,###"
  214.         .Borders.LineStyle = xlContinuous
  215.         .Borders.Weight = xlThin
  216.         .Borders.ColorIndex = xlAutomatic
  217.         .Borders(xlEdgeTop).Weight = xlMedium
  218.         .Borders(xlEdgeBottom).Weight = xlMedium
  219.         .Borders(xlEdgeRight).Weight = xlMedium
  220.         .Borders(xlEdgeLeft).Weight = xlMedium
  221.         .Interior.Color = RGB(118, 147, 60)
  222.     End With
  223. '---------------------------------------------------------------------------------------
  224. ' Loss %
  225. '---------------------------------------------------------------------------------------
  226.    FieldNumber = FieldNumber + 1
  227.     FieldTag = "loss"
  228.     Sheets(TargetSheetName).Range("A2").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Value = "Loss Rate"
  229.     Sheets(TargetSheetName).Range("A2").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Font.Italic = True
  230.     On Error Resume Next
  231.     For i = StartingSheet To EndingSheet
  232.         FieldRow = -1
  233.         FieldRow = Sheets(i).Range("B:B").Find(What:=FieldTag).Row
  234.         With Sheets(TargetSheetName).Range("A3").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Offset(i - StartingSheet, 0)
  235.             .Value = Sheets(i).Name
  236.             If (FieldRow > 0) Then
  237.                 For j = 1 To MonthsBackInTime
  238.                     .Offset(0, j).Value = Sheets(i).Range("A1").Offset(FieldRow - 1, _
  239.                         Sheets(i).Range("A1:XFD1048576").Find(What:=Sheets(TargetSheetName).Range("A1").Offset(0, j).Value).Column - 1).Value
  240.                 Next j
  241.             End If
  242.         End With
  243.     Next i
  244.     On Error GoTo 0
  245.     With Sheets(TargetSheetName).Range("A3").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Resize(EndingSheet - StartingSheet + 1, MonthsBackInTime + 1)
  246.         .NumberFormat = "0.00%"
  247.         .Borders.LineStyle = xlContinuous
  248.         .Borders.Weight = xlThin
  249.         .Borders.ColorIndex = xlAutomatic
  250.         .Borders(xlEdgeTop).Weight = xlMedium
  251.         .Borders(xlEdgeBottom).Weight = xlMedium
  252.         .Borders(xlEdgeRight).Weight = xlMedium
  253.         .Borders(xlEdgeLeft).Weight = xlMedium
  254.         .Interior.Color = RGB(118, 147, 60)
  255.     End With
  256. '---------------------------------------------------------------------------------------
  257. ' WALTV
  258. '---------------------------------------------------------------------------------------
  259.    FieldNumber = FieldNumber + 1
  260.     FieldTag = "ltv"
  261.     Sheets(TargetSheetName).Range("A2").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Value = "WA LTV"
  262.     Sheets(TargetSheetName).Range("A2").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Font.Italic = True
  263.     On Error Resume Next
  264.     For i = StartingSheet To EndingSheet
  265.         FieldRow = -1
  266.         FieldRow = Sheets(i).Range("B:B").Find(What:=FieldTag).Row
  267.         With Sheets(TargetSheetName).Range("A3").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Offset(i - StartingSheet, 0)
  268.             .Value = Sheets(i).Name
  269.             If (FieldRow > 0) Then
  270.                 For j = 1 To MonthsBackInTime
  271.                     .Offset(0, j).Value = Sheets(i).Range("A1").Offset(FieldRow - 1, _
  272.                         Sheets(i).Range("A1:XFD1048576").Find(What:=Sheets(TargetSheetName).Range("A1").Offset(0, j).Value).Column - 1).Value
  273.                 Next j
  274.             End If
  275.         End With
  276.     Next i
  277.     On Error GoTo 0
  278.     With Sheets(TargetSheetName).Range("A3").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Resize(EndingSheet - StartingSheet + 1, MonthsBackInTime + 1)
  279.         .NumberFormat = "0.00%"
  280.         .Borders.LineStyle = xlContinuous
  281.         .Borders.Weight = xlThin
  282.         .Borders.ColorIndex = xlAutomatic
  283.         .Borders(xlEdgeTop).Weight = xlMedium
  284.         .Borders(xlEdgeBottom).Weight = xlMedium
  285.         .Borders(xlEdgeRight).Weight = xlMedium
  286.         .Borders(xlEdgeLeft).Weight = xlMedium
  287.         .Interior.Color = RGB(118, 147, 60)
  288.     End With
  289. '---------------------------------------------------------------------------------------
  290. ' WA Size
  291. '---------------------------------------------------------------------------------------
  292.    FieldNumber = FieldNumber + 1
  293.     FieldTag = "loan"
  294.     Sheets(TargetSheetName).Range("A2").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Value = "WA Mortgage Size"
  295.     Sheets(TargetSheetName).Range("A2").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Font.Italic = True
  296.     On Error Resume Next
  297.     For i = StartingSheet To EndingSheet
  298.         FieldRow = -1
  299.         FieldRow = Sheets(i).Range("B:B").Find(What:=FieldTag).Row
  300.         With Sheets(TargetSheetName).Range("A3").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Offset(i - StartingSheet, 0)
  301.             .Value = Sheets(i).Name
  302.             If (FieldRow > 0) Then
  303.                 For j = 1 To MonthsBackInTime
  304.                     .Offset(0, j).Value = Sheets(i).Range("A1").Offset(FieldRow - 1, _
  305.                         Sheets(i).Range("A1:XFD1048576").Find(What:=Sheets(TargetSheetName).Range("A1").Offset(0, j).Value).Column - 1).Value
  306.                 Next j
  307.             End If
  308.         End With
  309.     Next i
  310.     On Error GoTo 0
  311.     With Sheets(TargetSheetName).Range("A3").Offset(FieldNumber * (EndingSheet - StartingSheet + 2), 0).Resize(EndingSheet - StartingSheet + 1, MonthsBackInTime + 1)
  312.         .NumberFormat = "#,###"
  313.         .Borders.LineStyle = xlContinuous
  314.         .Borders.Weight = xlThin
  315.         .Borders.ColorIndex = xlAutomatic
  316.         .Borders(xlEdgeTop).Weight = xlMedium
  317.         .Borders(xlEdgeBottom).Weight = xlMedium
  318.         .Borders(xlEdgeRight).Weight = xlMedium
  319.         .Borders(xlEdgeLeft).Weight = xlMedium
  320.         .Interior.Color = RGB(118, 147, 60)
  321.     End With
  322. End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!