Excel sheets with employees and dates I have more than 20 sheets in Excel and one main sheet (all programs with 200 names). Each sheet has a column with names and 24 months (Jan 18 to Dec 18, Jan 19 to Dec 20). Each sheet names is a subset of the main sheet.

Main sheet (all programs) has 200 names and 24 months (values to be calculated based on other sheets). The other sheet has names and values for each month respective to the main sheet. I need to take each name in main sheet and search the name in all other sheets, and if present sum all same column values and insert in the main sheet.

For 1 name I need to do calculation on 34 cells (for 200 names * 34 cells = 6800 cells). It's taking almost 20 minutes with my code. Is there any other way I can do it or any modification which improves the performance?

Main Sheet has name "employee1"



Sheet1



Sheet2



Value on the main sheet should be calculated respect to months



Dim sheetCount As Integer
Dim datatoFind
Private Sub CommandButton1_Click()
    Dim mainSheet As String: mainSheet = "All Programs"
    Dim nameColumnStart As String: nameColumnStart = "A"

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

    Dim startColumn As Integer: startColumn = 10 'J Column'
    Dim EndColumn As Integer: EndColumn = 33 'AG Column'

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


    Dim temp_str As String
    Dim total As Single
    On Error Resume Next
    Sheets(mainSheet).Activate
    lastRow_main = ActiveCell.SpecialCells(xlLastCell).Row
    lastCol_main = 34
    For vRow = namesStart To namesEnd
        temp_str = Sheets(mainSheet).Cells(vRow, "A").Text
        datatoFind = StrConv(temp_str, vbLowerCase)
            For vCol = startColumn To EndColumn
                total = Find_Data(vCol)
                Worksheets(mainSheet).Cells(vRow, vCol).Value = total
            Next vCol
    Next vRow
    Sheets(mainSheet).Activate
    'MsgBox ("Calculated all values")'
End Sub


Private Function Find_Data(ByVal ColumnName As Integer) As Single
    Dim counter As Integer
    Dim currentSheet As Integer
    Dim sheetCount As Integer
    Dim str As String
    Dim lastRow As Long
    Dim lastCol As Long
    Dim val As Single

    Find_Data = 0

    currentSheet = ActiveSheet.Index

    If datatoFind = "" Then Exit Function

    sheetCount = ActiveWorkbook.Sheets.Count

    For counter = 2 To sheetCount

        Sheets(counter).Activate

        lastRow = ActiveCell.SpecialCells(xlLastCell).Row
        lastCol = ActiveCell.SpecialCells(xlLastCell).Column

        For vRow = 1 To lastRow
                str = Sheets(counter).Cells(vRow, "A").Text
                If InStr(1, StrConv(str, vbLowerCase), datatoFind) Then
                        val = Sheets(counter).Cells(vRow, ColumnName).Value
                        Find_Data = Find_Data + val
                End If
        Next vRow

    Next counter

End Function
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 &lt; namesStartDefault Then
    MsgBox "Start vaule must be greater than or equal to " &amp; 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 &lt; namesStartDefault Then
        MsgBox "Start vaule must be greater than or equal to " &amp; 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" &amp; .Rows.Count).End(xlUp).Row
                    If InStr(1, .Cells(r, 1).Value, EmployeeName, vbTextCompare) &gt; 0 Then
                        result = result + .Cells(r, ColumnIndex).Value
                    End If
                Next
            End If
        End With
    Next
    Find_Data = result
End Function