Hi There i currently have a code written by a kind gentleman on this site, and what it did was auto sort depending on the name of the status a loan was at. Then If a loan was funded or declined it would move it to the respective sheet in the work book. I now have multiple branches (Sheets) and when i copied it over i get an error. The original code is below and works fine
Private Sub Worksheet_Change(ByVal Target As Range)
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Active_Loans")
Dim col As Range
Set col = Columns("G:G")
If Selection.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("G:G")) Is Nothing Then Exit Sub
Dim lr As Long, lr2 As Long
Application.EnableEvents = False
lr = Sheets("Loans Disbursed").Cells(Rows.Count, "G").End(xlUp).Row
lr2 = Sheets("Loans Declined").Cells(Rows.Count, "G").End(xlUp).Row
Select Case Target.Value
Case Is = "Disbursed"
Target.EntireRow.Copy
Sheets("Loans Disbursed").Rows("2:2").Insert
Target.EntireRow.Delete
Case Is = "Declined/Withdrawn"
Target.EntireRow.Copy
Sheets("Loans Declined").Rows("2:2").Insert
Target.EntireRow.Delete
End Select
With tbl.Sort
.SortFields.Clear
.SortFields.Add Key:=col, SortOn:=xlSortOnValues, Order:=xlDescending
.Header = xlYes
.Apply
End With
With Application
.EnableEvents = True
.CutCopyMode = False
End With
End Sub
but when i copied it i get this error see pic in uploads.
I think it has something to do with trying to replicate the code to other sheets. Is there anyway i can copy the code so it applies to any sheet i want. I tried renaming it to the sheet name but i get the runtime error 9 subscript out of range
Private Sub Worksheet_Change(ByVal Target As Range)
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Active_Loans")
Dim col As Range
Set col = Columns("G:G")
If Selection.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("G:G")) Is Nothing Then Exit Sub
Dim lr As Long, lr2 As Long
Application.EnableEvents = False
lr = Sheets("Loans Disbursed").Cells(Rows.Count, "G").End(xlUp).Row
lr2 = Sheets("Loans Declined").Cells(Rows.Count, "G").End(xlUp).Row
Select Case Target.Value
Case Is = "Disbursed"
Target.EntireRow.Copy
Sheets("Loans Disbursed").Rows("2:2").Insert
Target.EntireRow.Delete
Case Is = "Declined/Withdrawn"
Target.EntireRow.Copy
Sheets("Loans Declined").Rows("2:2").Insert
Target.EntireRow.Delete
End Select
With tbl.Sort
.SortFields.Clear
.SortFields.Add Key:=col, SortOn:=xlSortOnValues, Order:=xlDescending
.Header = xlYes
.Apply
End With
With Application
.EnableEvents = True
.CutCopyMode = False
End With
End Sub
but when i copied it i get this error see pic in uploads.
I think it has something to do with trying to replicate the code to other sheets. Is there anyway i can copy the code so it applies to any sheet i want. I tried renaming it to the sheet name but i get the runtime error 9 subscript out of range