Excel sheets with employees and dates

Global Variables

Dim sheetCount As Integer
Dim datatoFind

Global variables make the code harder to maintain, modify, and debug. It would be better to pass the data as parameters between the sub routines. This makes it easier to determine exactly what data is being passed into your subroutines.

Private or Public modifiers should be used instead of Dim when declaring a global variable.

Constant Expressions

Dim mainSheet As String: mainSheet = "All Programs"
Dim nameColumnStart As String: nameColumnStart = "A"
Dim startColumn As Integer: startColumn = 10 'J Column'
Dim EndColumn As Integer: EndColumn = 33 'AG Column'

The variables above should be declared as constants.

Const mainSheet As String = "All Programs"
Const nameColumnStart As String = "A"
Const startColumn As Integer = 10 'J Column'
Const EndColumn As Integer = 33 'AG Column'

namesStart and namesEnd

Why initiate the values below if you are not going to use the initial values?

Dim namesStart As Integer: namesStart = 1
Dim namesEnd As Integer: namesEnd = 200


namesStart = InputBox("Please enter start value")
namesEnd = InputBox("Please enter end value")

Consider using Application.InputBox because you can specify the type of data it returns.

Dim namesStart As Integer
Dim namesEnd As Integer
Const namesStartDefault As Integer = 1
Const namesEndDefault As Integer = 200

namesStart = Application.InputBox(Prompt:="Please enter start value", Default:=namesStartDefault, Type:=1)
namesEnd = Application.InputBox(Prompt:="Please enter end value", Default:=namesEndDefault, Type:=1)

If namesStart < namesStartDefault Then
    MsgBox "Start vaule must be greater than or equal to " & namesStartDefault, vbCritical
    Exit Sub
End If

Selecting and Activating Objects

Selecting and Activating Objects should be avoided unless absolutely necessary, watch Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset). I would wager to bet that of the 20 minutes that it takes to run your code 19+ minutes are spent needlessly activating worksheets.

Using Application.ScreenUpdating = False would probably cut the time in half.

Function Find_Data

SpecialCells(xlLastCell) should only be used when you don't know the data structure.

lastCol isn't used.

 lastCol = ActiveCell.SpecialCells(xlLastCell).Column

vRow is never declared. The v prefix implies a variant when it should clearly be long.

ColumnName implies a string value. I would use CoumnIndex instead.

Refactored Code

Private Const mainSheet As String = "All Programs"

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Const LastColumn = 34
    Dim namesStart As Integer
    Dim namesEnd As Integer
    Const namesStartDefault As Integer = 1
    Const namesEndDefault As Integer = 200

    namesStart = Application.InputBox(Prompt:="Please enter start value", Default:=namesStartDefault, Type:=1)
    namesEnd = Application.InputBox(Prompt:="Please enter end value", Default:=namesEndDefault, Type:=1)

    If namesStart < namesStartDefault Then
        MsgBox "Start vaule must be greater than or equal to " & namesStartDefault, vbCritical
        Exit Sub
    End If

    Dim r As Long, c As Long
    With ThisWorkbook.Worksheets(mainSheet)
        For r = namesStartDefault To namesEndDefault
            For c = 2 To LastColumn
                .Cells(r, c).Value = Find_Data(.Cells(r, 1).Value, c)
            Next
        Next
    End With

End Sub

Private Function Find_Data(ByVal EmployeeName As String, ByVal ColumnIndex As Integer) As Single
    Dim result As Single
    Dim ws As Worksheet
    Dim r As Long
    For Each ws In ThisWorkbook.Worksheets
        With ws
            If Not .Name = mainSheet Then
                For r = 2 To .Range("A" & .Rows.Count).End(xlUp).Row
                    If InStr(1, .Cells(r, 1).Value, EmployeeName, vbTextCompare) > 0 Then
                        result = result + .Cells(r, ColumnIndex).Value
                    End If
                Next
            End If
        End With
    Next
    Find_Data = result
End Function