For x = 1 To wkshtcount

    Set sh = ThisWorkbook.Sheets(x + 1)
    Set shdest = ThisWorkbook.Sheets(sh.name & (x + 1))
    Set rn = sh.UsedRange
    Set Names = sh.Range("A2:A" & Range("A1").End(xlDown).Row)
        
            For Each name In Names
                If name Like "*2" Then
                            shdest.Range("D7") = name
                            Else: shdest.Range("D8") = name

                End If
            Next name
Next x

    Set rn = sh.UsedRange
    k = rn.Rows.Count + rn.Row - 1
    For y = 2 To k
                If sh.Range("A" & y) Like "*2" Then
                            shdest.Range("D7") = sh.Range("A" & y)
                            Else: shdest.Range("D8") = sh.Range("A" & y)

                End If
    Next y

Sub WorksheetLoop()

         Dim WS_Count As Integer
         Dim I As Integer

         ' Set WS_Count equal to the number of worksheets in the active
         ' workbook.
         WS_Count = ActiveWorkbook.Worksheets.Count

         ' Begin the loop.
         For I = 1 To WS_Count

            ' Insert your code here.
            ' The following line shows how to reference a sheet within
            ' the loop by displaying the worksheet name in a dialog box.
            MsgBox ActiveWorkbook.Worksheets(I).Name

         Next I

      End Sub