I see you really are new to VBA. I will do my best to explain.
1.) With is used when you do not want to type the same reference on each
line. For example, in this case Sheets("Sheet1") is used as my reference and
both examples are equivalent.
Sheets("Sheet1").Name = "Test1"
Sheets("Sheet1").Range("A1").Value = "String"
or, I could write it using the With Statement
With Sheets("Sheet1")
.Name = "Test1"
.Range("A1").Value = "String"
End With
2.) The Select...Case Statement is like a fancy If...Then Statement. Both
examples below are equivalent.
If x = 1 Then
' do something
End If
If x = 2 Then
' do that
End If
If x = 3 Then
' do this
End If
or
Select Case x
Case 1
' do something
Case 2
' do that
Case 3
' do this
End Select
3.) Try this new code I gave you. You should not have to change anything
but the constants below. Change the strSourceWbkName, strSourceWksName, etc
as I have indicated below for you.
4.) If this code produces an error indicate to me which line caused the
code and what the error says.
If this helps! Click "YES" below.
Option Explicit
Sub SumHours()
Const strSourceWbkName As String = "Source Workbook Name Here"
Const strSourceWksName As String = "Source Worksheet Name Here"
Const strDestinationWbkName As String = "Destination Workbook Name Here"
Const strDestinationWksName As String = "Destination Worksheet Name Here"
Dim wksDestination As Worksheet
Dim wbkSource As Workbook
Dim wksSource As Worksheet
Dim lngLastWorkOrder As Long
Dim rngWorkOrders As Range
Dim lngLastRow As Long
Dim rngSource As Range
Dim cell As Range
Dim rngFoundOrder As Range
Set wksDestination =
Workbooks(strDestinationWbkName).Sheets(strDestinationWksName)
Set wbkSource = Workbooks.Open("H:\FAC\DaveSipes\" & strSourceWbkName,
UpdateLinks:=False, ReadOnly:=True)
Set wksSource = Workbooks(strSourceWbkName).Sheets(strSourceWksName)
With wksDestination
' find last row in workorder col
lngLastWorkOrder = .Cells(Rows.Count, "B").End(xlUp).Row
If lngLastWorkOrder < 6 Then
MsgBox "There are no workorders to process.", vbInformation
Exit Sub
End If
' set range of work orders
Set rngWorkOrders = .Range("B6:B" & lngLastWorkOrder)
End With
With wksSource
' find last row in source wks
lngLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
If lngLastWorkOrder < 2 Then
MsgBox "There are no workorders to process.", vbInformation
Exit Sub
End If
'set range to scan
Set rngSource = .Range("A2:A" & lngLastRow)
End With
' find workorders in source wks
For Each cell In rngWorkOrders
Set rngFoundOrder = rngSource.Find _
(What:=cell.Value, _
LookIn:=xlValues, _
LookAt:=xlWhole)
' if workorder if found add the hours to the destination wks
If Not rngFoundOrder Is Nothing Then
Select Case Format(rngFoundOrder.Offset(0, 1).Value, "dddd")
Case "Monday"
With wksDestination.Cells(cell.Row, "C")
.Value = .Value + wksSource.Cells(rngFoundOrder.Row,
"D").Value
End With
Case "Tuesday"
With wksDestination.Cells(cell.Row, "D")
.Value = .Value + wksSource.Cells(rngFoundOrder.Row,
"D").Value
End With
Case "Wednesday"
With wksDestination.Cells(cell.Row, "E")
.Value = .Value + wksSource.Cells(rngFoundOrder.Row,
"D").Value
End With
Case "Thursday"
With wksDestination.Cells(cell.Row, "F")
.Value = .Value + wksSource.Cells(rngFoundOrder.Row,
"D").Value
End With
Case "Friday"
With wksDestination.Cells(cell.Row, "G")
.Value = .Value + wksSource.Cells(rngFoundOrder.Row,
"D").Value
End With
Case "Saturday"
With wksDestination.Cells(cell.Row, "H")
.Value = .Value + wksSource.Cells(rngFoundOrder.Row,
"D").Value
End With
Case "Sunday"
With wksDestination.Cells(cell.Row, "I")
.Value = .Value + wksSource.Cells(rngFoundOrder.Row,
"D").Value
End With
End Select
End If
Next cell
End Sub
