Excel sheets with employees and dates

Question

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"

enter image description here

Sheet1

enter image description here

Sheet2

enter image description here

Value on the main sheet should be calculated respect to months

enter image description here

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

• Toby Speight: Welcome to Code Review! The current question title, which states your concerns about the code, applies to too many questions on this site to be useful. The site standard is for the title to simply state the task accomplished by the code. Please see How to Ask for examples, and revise the title accordingly.
• Mathieu Guindon: I've removed the VBScript tag, since this is obviously VBA - and VBScript is a different language. What Toby means is that as it stands, the post's title is essentially "my code runs too slow, how do I make it faster" - which is a title that's applicable to pretty much 90% of the VBA questions on this site. So in order to avoid having a vba page filled with nearly-identical titles, we ask that you make your title a short description of what your code does, i.e. it's purpose. As the watermark says: "state the purpose of the code". Thanks
• Rohith Gowda: i changed the title for easy filtering . Thank you @m
• Mathieu Guindon: That's.... literally the opposite of what I said.
• Rohith Gowda: Got it , I will change lol

Answer 1

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