Alttaki kodu kullanırsanız son sütun numarasını otomatik bulup orda Görevlilerin tarihini getirir.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim bul As Range, i As Integer, say As Integer

Dim sonSutun As Integer, ilkSutun As Byte



With Sayfa7

   .Range(.Range("C9"), .Cells(16, Columns.Count)).ClearContents

    ilkSutun = 8 'Tarih baslangis sütun

    sonSutun = Cells(5, Columns.Count).End(xlToLeft).Column

    If sonSutun < ilkSutun Then

        MsgBox "Tarih yok..", vbCritical, "Hata"

        Exit Sub

    End If

    

    If Not Intersect(Target, Range("A6:A" & Rows.Count)) Is Nothing Then Exit Sub

    Set bul = Range(Cells(Target.Row, ilkSutun), Cells(Target.Row, Columns.Count)).Find("Görevli", , xlValues, 1)

    say = 3

        .Range("C9").Value = Target.Value

        .Range("C10").Value = Target.Offset(, 3).Value

        .Range("C12").Value = Target.Offset(, 1).Value

        .Range("C11").Value = Target.Offset(, 2).Value

    If Not bul Is Nothing Then

        For i = ilkSutun To sonSutun

            If LCase(Cells(bul.Row, i).Value) = "görevli" Then

                .Cells(15, say).Value = Cells(5, i).Value

                say = say + 1

            End If

        Next

    End If

End With

Set bul = Nothing

MsgBox "Aktarildi", vbInformation + vbOKOnly, "Bitti"

End Sub