My vba code to copy row A110 of "Mod" worksheet to A9 in Table A of "Email Data" worksheet using some logic like -
in Mod worksheet it searches for data i.e. "COUNT(DISTINCTM.TRANS_ID)" if found then it searches for "row selected" and it searches for data between the two . And then it pastes 34864 to A9 of Email Data worksheet . I have written code for this but it doesn't works.

Also i need vba code similarly for searching "CAP_ACTV_LN_SEQ" and then the values inside it to be pasted i.e. row 128 and row 129 in Mod worksheet to be pasted in table e in Email Data worksheet . Remember there are 2 rows but there can be any number of row anytime.

Also the vba code for "NULLPOINTEREXCEPTION'" is there in the sheet which is working fine. It can be used for your reference.

Button for Vba code to work is present in "Email Data" worksheet.

code for "COUNT(DISTINCTM.TRANS_ID)" given below also excel sheet attached. I am very week in vba programming so any help will be great .

Worksheet .xlsm

Sub DistictCountBlocks()

    Dim iCount As Integer
    Dim r As Long, j As Long
    Dim lr As Long
    Dim TotalTransaction As Integer
    
    Application.ScreenUpdating = False
    
    With wksMod
    
        'The last row in Mod worksheet
        lr = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        For r = 1 To lr
            If .Cells(r, 1) = "COUNT(DISTINCTM.TRANS_ID)" Then
                j = r 'tracker varaible or something
                TotalTransaction = 0
                While Not .Cells(j, 2) & " " & .Cells(j, 3) = "rows selected"
                 j = 108 + 2
                    TotalTransaction = .Cells(j, 1)
                    
                Wend
                With wksEmail
                    
                            .Cells(9, "A") = TotalTransaction
                            
                End With
            End If
            
            
        Next
        
    End With

End Sub