Sub Timesheet_Counter()

Dim i, counter As Long
Dim c1 As Range
Dim source, newsheet As Worksheet
Dim search_string As String

Set source = Sheets("2019")
Set newsheet = Sheets.Add

newsheet.Name = "Summary" & Format(Date, "ddmmyy") & Format(Time, "hhmmss")

search_string = "J&J098"
counter = 1

For i = 1 To 1000
    Set c1 = source.Cells(i, 3)
    Set c2 = source.Cells(i, 6)
    Set c3 = source.Cells(i, 9)
    Set c4 = source.Cells(i, 12)
    Set c5 = source.Cells(i, 15)

    If c1.Value = search_string Then
        counter = counter + 1

        newsheet.Cells(counter, 1).Value = source.Cells(i, 2) 'Date
        newsheet.Cells(counter, 2).Value = c1 'Project Code
        newsheet.Cells(counter, 3).Value = c1.Offset(, 1) ' Hours
        newsheet.Cells(counter, 4).Value = c1.Offset(, 2) ' Type

        'Debug.Print c1
        'Debug.Print c1.Offset(, -1)
        'Debug.Print c1.Offset(, 1)
        'Debug.Print c1.Offset(, 2)
    End If

    If c2.Value = search_string Then
        counter = counter + 1

        newsheet.Cells(counter, 1).Value = source.Cells(i, 2) 'Date
        newsheet.Cells(counter, 2).Value = c2 'Project Code
        newsheet.Cells(counter, 3).Value = c2.Offset(, 1) ' Hours
        newsheet.Cells(counter, 4).Value = c2.Offset(, 2) ' Type
    End If

    If c3.Value = search_string Then
        counter = counter + 1

        newsheet.Cells(counter, 1).Value = source.Cells(i, 2) 'Date
        newsheet.Cells(counter, 2).Value = c3 'Project Code
        newsheet.Cells(counter, 3).Value = c3.Offset(, 1) ' Hours
        newsheet.Cells(counter, 4).Value = c3.Offset(, 2) ' Type
    End If

    If c4.Value = search_string Then
        counter = counter + 1

        newsheet.Cells(counter, 1).Value = source.Cells(i, 2) 'Date
        newsheet.Cells(counter, 2).Value = c4 'Project Code
        newsheet.Cells(counter, 3).Value = c4.Offset(, 1) ' Hours
        newsheet.Cells(counter, 4).Value = c4.Offset(, 2) ' Type
    End If

    If c5.Value = search_string Then
        counter = counter + 1

        newsheet.Cells(counter, 1).Value = source.Cells(i, 2) 'Date
        newsheet.Cells(counter, 2).Value = c5 'Project Code
        newsheet.Cells(counter, 3).Value = c5.Offset(, 1) ' Hours
        newsheet.Cells(counter, 4).Value = c5.Offset(, 2) ' Type
    End If

Next

End Sub