Range variable not populating

[mikenelena]

This code was working fine, but for some reason, it has stopped functioning. There are no errors. From what I can tell stepping through the code, my apprasierNamesRange variable is simply not populating with any data at this step:

' Set the appraiser names range to only include the rows with the most recent payroll date
Set appraiserNamesRange = mainSheet.Range("A:C").Find(What:=lastPayrollDate, LookIn:=xlValues, LookAt:=xlWhole)

The idea behind this code was to copy rows from a main worksheet to individual employee worksheets. Copy would be based on the most recent payroll date (sorted in column B), and pasted based on the employee name in column A. The worksheet names are obtained through a Vlookup to a worksheet called Generals. I didn't knowingly make any changes to the code. I did do an Office 365 update today, but that could be a coincidence. If it matters, my dates are formatted as mm-dd-yyyy. Can anyone see any issues with this code? Thanks in advance!

Sub GetPayrollData()

Dim mainSheet As Worksheet
Dim generalsSheet As Worksheet
Dim payrollDatesRange As Range
Dim lastPayrollDate As Date
Dim appraiserNamesRange As Range
Dim appraiserName As String
Dim destinationSheetName As String
Dim destinationSheet As Worksheet
Dim i As Long

' Set references to the relevant worksheets
Set mainSheet = ThisWorkbook.Worksheets("Main")
Set generalsSheet = ThisWorkbook.Worksheets("Generals")

Application.ScreenUpdating = False

' Determine the most recent payroll date
Set payrollDatesRange = mainSheet.Range("B2:B" & mainSheet.Range("B" & mainSheet.Rows.Count).End(xlUp).Row)

lastPayrollDate = WorksheetFunction.Max(payrollDatesRange)

' Set the appraiser names range to only include the rows with the most recent payroll date
Set appraiserNamesRange = mainSheet.Range("A:C").Find(What:=lastPayrollDate, LookIn:=xlValues, LookAt:=xlWhole)


    If Not appraiserNamesRange Is Nothing Then
        Set appraiserNamesRange = appraiserNamesRange.Resize(mainSheet.Range("A:C").End(xlDown).Row - appraiserNamesRange.Row + 1, 3)

        ' Loop through the appraiser names range and copy/paste the data to the appropriate worksheets

        For i = 1 To appraiserNamesRange.Rows.Count
                    appraiserName = appraiserNamesRange.Cells(i, 0).Value
                    destinationSheetName = Application.VLookup(appraiserName, generalsSheet.Range("A:H"), 8, False)
        
                    If Not IsError(destinationSheetName) Then
                        Set destinationSheet = ThisWorkbook.Worksheets(destinationSheetName)
                        Dim copyRange As Range
                        Dim lngFirstFree As Long
                        lngFirstFree = destinationSheet.Cells(destinationSheet.Rows.Count, 1).End(xlUp).Row + 1
        
                        Set copyRange = mainSheet.Range("C" & appraiserNamesRange.Row + i - 1).Resize(1, 2)
        
                        destinationSheet.Cells(lngFirstFree, "A").Resize(1, 2).Value = copyRange.Value
                        destinationSheet.Cells(lngFirstFree, 2).NumberFormat = "mm-dd-yyyy"
        
                        Set copyRange = mainSheet.Range("G" & appraiserNamesRange.Row + i - 1).Resize(1, 2)
                        destinationSheet.Cells(lngFirstFree, "E").Resize(1, 2).Value = copyRange.Value
        
                    End If
        
                Next i

    Else

        MsgBox "No data found for the most recent payroll date."

    End If

Application.ScreenUpdating = True

' Clear clipboard and display a message
Application.CutCopyMode = False

MsgBox "Data has been copied to the appropriate worksheets. The payroll is now ready for upload to Truist. Please look it over carefully for mistakes, e-mail the worksheets to the appraisers, and add a new payroll period."

End Sub

 

[mikenelena]

I ended up taking this in a different direction to avoid the error. This is my working code now:

Sub GetPayrollData()

Dim wsMain As Worksheet
Dim wsGenerals As Worksheet
Dim wsAppraiser As Worksheet
Dim lRow As Long
Dim lCol As Long
Dim lApprRow As Long
Dim strApprName As String
Dim strApprSheet As String
Dim dtMostRecentPayrollDate As Date
Dim arrData() As Variant
Dim i As Long
Dim j As Long

Set wsMain = Worksheets("Main")
Set wsGenerals = Worksheets("Generals")

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.DisplayAlerts = False

'Find the most recent payroll date
lRow = wsMain.Cells(Rows.Count, "B").End(xlUp).Row
dtMostRecentPayrollDate = wsMain.Cells(lRow, "B").Value

'Load Generals sheet into an array
Dim arrGenerals() As Variant
arrGenerals = wsGenerals.Range("A2:H" & wsGenerals.Cells(Rows.Count, "A").End(xlUp).Row)

'Loop through all rows on Main containing the most recent payroll date
For lRow = lRow To 1 Step -1
If wsMain.Cells(lRow, "B").Value = dtMostRecentPayrollDate Then
'Find the Appraiser Name from Column A
strApprName = wsMain.Cells(lRow, "A").Value
'Find the Appraiser Worksheet Name
For i = LBound(arrGenerals, 1) To UBound(arrGenerals, 1)
If arrGenerals(i, 1) = strApprName Then
strApprSheet = arrGenerals(i, 8)
Exit For
End If
Next i
'Set the Appraiser Worksheet
Set wsAppraiser = Worksheets(strApprSheet)
'Find the next empty row on the Appraiser Worksheet
lApprRow = wsAppraiser.Cells(Rows.Count, "A").End(xlUp).Row + 1
'Copy data from Main into an array
arrData = wsMain.Range("C" & lRow & ":H" & lRow).Value
'Write the array to Appraiser
For i = LBound(arrData, 1) To UBound(arrData, 1)
For j = LBound(arrData, 2) To UBound(arrData, 2)
If Not IsEmpty(arrData(i, j)) Then
wsAppraiser.Cells(lApprRow + i - 1, j).Value = arrData(i, j)
End If
Next j
Next i
End If
Next lRow

'Reset application settings
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub