Sub cmbLnOffNum_Change()
Dim idx As Long
Dim LnOffRow As Double
Dim LnFinOffRow As Long
Dim Lws As Worksheet
Dim ws As Worksheet
Dim rng As Range
Set ws = Worksheets("Oct_2012")
idx = cmbLnOffNum.ListIndex
If idx <> -1 Then
cmbLnOff.Value = Worksheets("LoanOfficers").Range("B" & idx + 4).Value
cmbBranch.Value = Worksheets("LoanOfficers").Range("C" & idx + 4).Value
Worksheets("FinalOutput").Range("B10") = cmbLnOff.Value
Worksheets("FinalOutput").Range("B11") = cmbBranch.Value
Worksheets("FinalOutput").Range("B12") = Worksheets("Oct_2012").Range("B4")
Worksheets("FinalOutput").Range("B13") = txtDate.Value
With ws
LnFinOffRow = .Cells(.Rows.Count, "D").End(xlUp).Row
.Range("D7:D" & LnFinOffRow).AutoFilter Field:=1, Criteria1:=cmbLnOffNum.Value
Application.EnableEvents = False
Worksheets("FinalOutput").Cells(17, 2).Formula = "=SUBTOTAL(109,Oct_2012!" & "A8:A" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(18, 2).Formula = "=SUBTOTAL(109,Oct_2012!" & "B8:B" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(19, 2).Formula = "=SUBTOTAL(109,Oct_2012!" & "C8:C" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(17, 3).Formula = "=SUBTOTAL(102,Oct_2012!" & "A8:A" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(18, 3).Formula = "=SUBTOTAL(102,Oct_2012!" & "B8:B" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(19, 3).Formula = "=SUBTOTAL(102,Oct_2012!" & "C8:C" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(17, 2).Value = Worksheets("FinalOutput").Cells(17, 2).Value * 1
Worksheets("FinalOutput").Cells(18, 2).Value = Worksheets("FinalOutput").Cells(18, 2).Value * 1
Worksheets("FinalOutput").Cells(19, 2).Value = Worksheets("FinalOutput").Cells(19, 2).Value * 1
Worksheets("FinalOutput").Cells(17, 3).Value = Worksheets("FinalOutput").Cells(17, 3).Value * 1
Worksheets("FinalOutput").Cells(18, 3).Value = Worksheets("FinalOutput").Cells(18, 3).Value * 1
Worksheets("FinalOutput").Cells(19, 3).Value = Worksheets("FinalOutput").Cells(19, 3).Value * 1
Worksheets("FinalOutput").Cells(11, 10).Formula = "=SUBTOTAL(109,Oct_2012!" & "G8:G" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(12, 10).Formula = "=SUBTOTAL(109,Oct_2012!" & "H8:H" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(13, 10).Formula = "=SUBTOTAL(109,Oct_2012!" & "I8:I" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(14, 10).Formula = "=SUBTOTAL(109,Oct_2012!" & "J8:J" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(15, 10).Formula = "=SUBTOTAL(109,Oct_2012!" & "K8:K" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(16, 10).Formula = "=SUBTOTAL(109,Oct_2012!" & "L8:L" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(17, 10).Formula = "=SUBTOTAL(109,Oct_2012!" & "M8:M" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(18, 10).Formula = "=SUBTOTAL(109,Oct_2012!" & "N8:N" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(19, 10).Formula = "=SUBTOTAL(109,Oct_2012!" & "O8:O" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(20, 10).Formula = "=SUBTOTAL(109,Oct_2012!" & "P8:P" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(21, 10).Formula = "=SUBTOTAL(109,Oct_2012!" & "Q8:Q" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(22, 10).Formula = "=SUBTOTAL(109,Oct_2012!" & "R8:R" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(23, 10).Formula = "=SUBTOTAL(109,Oct_2012!" & "S8:S" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(24, 10).Formula = "=SUBTOTAL(109,Oct_2012!" & "T8:T" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(11, 9).Formula = "=SUBTOTAL(102,Oct_2012!" & "G8:G" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(12, 9).Formula = "=SUBTOTAL(102,Oct_2012!" & "H8:H" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(13, 9).Formula = "=SUBTOTAL(102,Oct_2012!" & "I8:I" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(14, 9).Formula = "=SUBTOTAL(102,Oct_2012!" & "J8:J" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(15, 9).Formula = "=SUBTOTAL(102,Oct_2012!" & "K8:K" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(16, 9).Formula = "=SUBTOTAL(102,Oct_2012!" & "L8:L" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(17, 9).Formula = "=SUBTOTAL(102,Oct_2012!" & "M8:M" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(18, 9).Formula = "=SUBTOTAL(102,Oct_2012!" & "N8:N" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(19, 9).Formula = "=SUBTOTAL(102,Oct_2012!" & "O8:O" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(20, 9).Formula = "=SUBTOTAL(102,Oct_2012!" & "P8:P" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(21, 9).Formula = "=SUBTOTAL(102,Oct_2012!" & "Q8:Q" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(22, 9).Formula = "=SUBTOTAL(102,Oct_2012!" & "R8:R" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(23, 9).Formula = "=SUBTOTAL(102,Oct_2012!" & "S8:S" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(24, 9).Formula = "=SUBTOTAL(102,Oct_2012!" & "T8:T" & LnFinOffRow & ")"
Worksheets("FinalOutput").Cells(11, 9).Value = Worksheets("FinalOutput").Cells(11, 9).Value * 1
Worksheets("FinalOutput").Cells(12, 9).Value = Worksheets("FinalOutput").Cells(12, 9).Value * 1
Worksheets("FinalOutput").Cells(13, 9).Value = Worksheets("FinalOutput").Cells(13, 9).Value * 1
Worksheets("FinalOutput").Cells(14, 9).Value = Worksheets("FinalOutput").Cells(14, 9).Value * 1
Worksheets("FinalOutput").Cells(15, 9).Value = Worksheets("FinalOutput").Cells(15, 9).Value * 1
Worksheets("FinalOutput").Cells(16, 9).Value = Worksheets("FinalOutput").Cells(16, 9).Value * 1
Worksheets("FinalOutput").Cells(17, 9).Value = Worksheets("FinalOutput").Cells(17, 9).Value * 1
Worksheets("FinalOutput").Cells(18, 9).Value = Worksheets("FinalOutput").Cells(18, 9).Value * 1
Worksheets("FinalOutput").Cells(19, 9).Value = Worksheets("FinalOutput").Cells(19, 9).Value * 1
Worksheets("FinalOutput").Cells(20, 9).Value = Worksheets("FinalOutput").Cells(20, 9).Value * 1
Worksheets("FinalOutput").Cells(21, 9).Value = Worksheets("FinalOutput").Cells(21, 9).Value * 1
Worksheets("FinalOutput").Cells(22, 9).Value = Worksheets("FinalOutput").Cells(22, 9).Value * 1
Worksheets("FinalOutput").Cells(23, 9).Value = Worksheets("FinalOutput").Cells(23, 9).Value * 1
Worksheets("FinalOutput").Cells(24, 9).Value = Worksheets("FinalOutput").Cells(24, 9).Value * 1
Worksheets("FinalOutput").Cells(11, 10).Value = Worksheets("FinalOutput").Cells(11, 10).Value * 1
Worksheets("FinalOutput").Cells(12, 10).Value = Worksheets("FinalOutput").Cells(12, 10).Value * 1
Worksheets("FinalOutput").Cells(13, 10).Value = Worksheets("FinalOutput").Cells(13, 10).Value * 1
Worksheets("FinalOutput").Cells(14, 10).Value = Worksheets("FinalOutput").Cells(14, 10).Value * 1
Worksheets("FinalOutput").Cells(15, 10).Value = Worksheets("FinalOutput").Cells(15, 10).Value * 1
Worksheets("FinalOutput").Cells(16, 10).Value = Worksheets("FinalOutput").Cells(16, 10).Value * 1
Worksheets("FinalOutput").Cells(17, 10).Value = Worksheets("FinalOutput").Cells(17, 10).Value * 1
Worksheets("FinalOutput").Cells(18, 10).Value = Worksheets("FinalOutput").Cells(18, 10).Value * 1
Worksheets("FinalOutput").Cells(19, 10).Value = Worksheets("FinalOutput").Cells(19, 10).Value * 1
Worksheets("FinalOutput").Cells(20, 10).Value = Worksheets("FinalOutput").Cells(20, 10).Value * 1
Worksheets("FinalOutput").Cells(21, 10).Value = Worksheets("FinalOutput").Cells(21, 10).Value * 1
Worksheets("FinalOutput").Cells(22, 10).Value = Worksheets("FinalOutput").Cells(22, 10).Value * 1
Worksheets("FinalOutput").Cells(23, 10).Value = Worksheets("FinalOutput").Cells(23, 10).Value * 1
Worksheets("FinalOutput").Cells(24, 10).Value = Worksheets("FinalOutput").Cells(24, 10).Value * 1
'Filters loan information for members written by Loan Officer
Worksheets("Oct_2012").Range("v8").Copy
Worksheets("FinalOutput").Activate
ActiveSheet.Paste Destination: Worksheets("FinalOutput").Rows ("LnOffRow")
Application.EnableEvents = True
.AutoFilterMode = False
End With
End If
End Sub
Nika
Bookmarks