Find last row of sheet based off cell value to reference the sheet name

[ItalianPlatinum]

I have a interesting dilemma where I am trying to deploy a formula that seems to only work If used range are utilized. The problem is the formula must be dynamic to reference the left most first character for the sheet name to reference but I am not sure how to replicate that to find the last row on its respective sheet?

My next item H ends on row 5,000, and the one after I ends on 10,000. I am setting this all via VBA. In previous code I do loop through the sheets setting the range as I go but I logically don't think I can reference it for this. I am only including the VBA if you think I can utilize it in any way.

	COUNTIFS(INDIRECT(LEFT(A3,1)&"!$D$2:$D$2217"),A3,INDIRECT(LEFT(A3,1)&"!$Q$2#"),">1")
List	Eligible
G12345678	45

' Run loop for range, clear, run, copy and paste into its respective sheet
  i = 0
  Do Until WsCus.Range("FILTER").Offset(i, 0) = ""
    FILTER = WsCus.Range("FILTER").Offset(i, 0)
    SheetName = WsCus.Range("FILTER").Offset(i, -1).Value 'Assuming from your screenshot it's in the column left of the filter

'apply filter to start loop and activate sheet
With WsSec
    .Range("F4") = FILTER
    .Application.Calculation = xlManual
    .Activate
End With

    Call Clear
    Call S2
  
'aftter execution copy from source and paste into relative sheet applying formulas
With WsSec
lastRow = .Cells(WsSec.rows.count, "A").End(xlUp).row 'find the maximum row
    .Range("A10:L" & lastRow).Copy
End With

On Error Resume Next
    If Not Worksheets(SheetName).Name = WsCus.Range("FILTER").Offset(i, -1).Value Then Worksheets.Add.Name = WsCus.Range("FILTER").Offset(i, -1).Value
On Error GoTo 0

With Worksheets(SheetName)
        .Range("A1").PasteSpecial Paste:=xlPasteValues
        .Application.CutCopyMode = False 'Clear the copy-paste memory
lr1 = .Cells(rows.count, "A").End(xlUp).row
        .Range("M1:Q1") = Array("Rounded 4 digit Cost", "Greater than 1yr", "Greater than 3yr", "For formula", "Same Cost Occurrence")
        .Application.Calculation = xlAutomatic
        .Range("P2:P" & lr1).NumberFormat = "General"
        .Range("M2:M" & lr1).Formula = "=Round(K2, 4)"
        .Range("N2:N" & lr1).Formula = "=if(RUN-E2>365,""YES"",""NO"")"
        .Range("O2:O" & lr1).Formula = "=if(RUN-E2>(365*3),""YES"",""NO"")"
        .Range("P2:P" & lr1).Formula = "=D2&M2"
        .Range("P2:P" & lr1).NumberFormat = "@"
        .Range("Q2").Formula2 = "=COUNTIF($P$2:$P$" & lr1 & ",$P$2:$P$" & lr1 & ")"
        '.Range("P2").Formula2 = "=COUNTIFS($D$2:$D$" & lr1 & ",$D$2:$D$" & lr1 & ",$M$2:$M$" & lr1 & ",$M$2:$M$" & lr1 & ")"
        .Range("M2:P" & lr1).Value = .Range("M2:P" & lr1).Value
        .Cells.EntireColumn.AutoFit
        .Activate
End With

ActiveWindow.ScrollRow = 1 'the row you want to scroll to
ActiveWindow.ScrollColumn = 1 'the column you want to scroll to
  
    i = i + 1
Loop

With WsSec
    .Activate
End With
Call Clear

With WsSum
    .Range("B2:B" & lr1).Formula2 = "=COUNTIFS(INDIRECT(LEFT(A2,1)&""!$D$2:$D$2217""),A2,INDIRECT(LEFT(A2,1)&""!$Q$2#""),"">1"")"
End With