Create Report based on criteria match

[Energizey]

Hi everyone,

Appreciate help with code I require to create a report based on one or more criteria being met.

I have source data in one worksheet and destination worksheet where I want the report generated. I have three cells were criteria can be selected, I need the code to work regardless of whether one or all three criteria cells contain fields - Criteria 1 = Employee Name C2, Criteria 2 = Modality C3, Criteria 3 = Department C4.

I need all rows from source worksheet columns A:W, to be copied and pasted to destination worksheet from Row 7 down if criteria is met. So far I have only tried to tackle code if a match is found to Criteria 1 Employee Name, but cannot get that to work. The code I have so far is as follows -

Sub ConsolidatePayroll_singlecriteria()
'1. declare and set variables
'2. clear old search results
'3. find records that match criteria and paste them to the report sheet
Dim datasheet As Worksheet 'where data is to be copied from
Dim reportsheet As Worksheet 'where data is pasted to
Dim employeename As String
Dim finalrow As Integer
Dim i As Integer 'row counter
'set variables
Set datasheet = Sheet7
Set reportsheet = Sheet11
employeename = reportsheet.Range("C2").Value
'clear old data from report sheet
reportsheet.Range("A7:V200").ClearContents 'adjust range to be cleared based upon size of data
'goto datasheet and start searching and copying
datasheet.Select
finalrow = Cells(Row.Count, 1).End(xlUp).Row
'loop through the rows to find the matching records
For i = 2 To finalrow
    If Cells(i, 3) = employeename Then 'if column C matches the search name then copy all rows
    Range(Cells(i, 1), Cells(i, 22)).Copy 'copy columns 1 to 22 (A to V)
    reportsheet.Select 'go to report sheet
    Range("A200").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats ' find the first blank cell and paste row
    datasheet.Select 'go back to the data sheet and continue searching
    End If
    
Next i
reportsheet.Select 'this is so that the report sheet is selected when the procedure ends
Range("B2").Select
End Sub