Hi all,

I've built a simple inventory tracking system, and decided a reporting feature would be nice. There are four categories that are entered when inventory is removed...Date, Employee Name, Item Description, Location, and quantity.

Four my reporting purposes I'm only concerned with Date, Employee name and Item Description.

I've been able to write code that does what I want using a multiple Cases and a For loop once the case is identified. However, the more data there is the longer this takes...so I decided to stretch myself and try my hand at arrays (first time really working with arrays), but I'm having trouble figuring out exactly what I need to do.

Here is what I think the steps need to be.

1. Store my data (the categories above) which are located in the Check Out sheet
2. Go through the arrayed data to find exact matches based on my search criteria (here is where the Cases come in)
3. Pull out only that data and write the information to a "Report" Sheet
4. Export that sheet to PDF (this part I already have)

Below is a copy of what my current "working" code looks like (I should mention that the search selections are made from a userform this is what the Cases are deciphering between which ones are blank etc...), also most of my variables are instantiated as Public variables within a Public_Variables module also below.

Select Case EmpList
    Case Is = vbNullString 'case when employee is not selected
        Select Case ItemBox
            Case Is = vbNulString 'When No Item is Selected
                Select Case IsDt
                    Case Is = True 'No Item Is Selected but there is a date
                        For Each Cel In CCDate
                            If Cel.Value >= FromDate Or Cel.Value <= ToDate Then
                                Cel.EntireRow.Copy
                                RepRng.Offset(1, 0).PasteSpecial
                                Set RepRng = wsReport.Range("A" & Rows.Count).End(xlUp)
                            End If
                        Next
                        Application.CutCopyMode = False
                End Select 'IsDt for case when item is not selected
            Case Is <> vbNulString 'Item is Selected
                Select Case IsDt
                    Case Is = True 'Item is selected with date
                        For Each Cel In CCDate
                            If Cel.Offset(0, 2).Value = ItemBox.Text And Cel.Value >= FromDate Or Cel.Value <= ToDate Then
                                Cel.EntireRow.Copy
                                RepRng.Offset(1, 0).PasteSpecial
                                Set RepRng = wsReport.Range("A" & Rows.Count).End(xlUp)
                            End If
                        Next
                        Application.CutCopyMode = False
                    Case Is = False 'Item is selected without date
                        For Each Cel In CCDate
                            If Cel.Offset(0, 2).Value = ItemBox.Text Then
                                Cel.EntireRow.Copy
                                RepRng.Offset(1, 0).PasteSpecial
                                Set RepRng = wsReport.Range("A" & Rows.Count).End(xlUp)
                            End If
                        Next
                        Application.CutCopyMode = False
                End Select 'IsDt for case when item is selected
        End Select 'ItemBox
    Case Is <> vbNullString 'Case when Employee is selected
        Select Case ItemBox
            Case Is = vbNulString 'When employee is selected but no Item is Selected
                Select Case IsDt
                    Case Is = True 'Employee is selected, no Item Is Selected, but there is a date range
                        For Each Cel In CCDate
                            If Cel.Offset(0, 1).Value = EmpList.Text And Cel.Value >= FromDate Or Cel.Value <= ToDate Then
                                Cel.EntireRow.Copy
                                RepRng.Offset(1, 0).PasteSpecial
                                Set RepRng = wsReport.Range("A" & Rows.Count).End(xlUp)
                            End If
                        Next
                        Application.CutCopyMode = False
                    Case Is = False
                        For Each Cel In CCDate
                            If Cel.Offset(0, 1).Value = EmpList.Text Then
                                Cel.EntireRow.Copy
                                RepRng.Offset(1, 0).PasteSpecial
                                Set RepRng = wsReport.Range("A" & Rows.Count).End(xlUp)
                            End If
                        Next
                        Application.CutCopyMode = False
                End Select 'IsDt for case when item is not selected
            Case Is <> vbNulString 'Employee is selected and Item is Selected
                Select Case IsDt
                    Case Is = True 'Employee is selected, Item is selected with date
                        For Each Cel In CCDate
                            If Cel.Offset(0, 1).Value = EmpList.Text And Cel.Offset(0, 2).Value = ItemBox.Text And Cel.Value >= FromDate Or Cel.Value <= ToDate Then
                                Cel.EntireRow.Copy
                                RepRng.Offset(1, 0).PasteSpecial
                                Set RepRng = wsReport.Range("A" & Rows.Count).End(xlUp)
                            End If
                        Next
                        Application.CutCopyMode = False
                    Case Is = False 'Employee is selected, Item is selected without date
                        For Each Cel In CCDate
                            If Cel.Offset(0, 1).Value = EmpList.Text And Cel.Offset(0, 2).Value = ItemBox.Text Then
                                Cel.EntireRow.Copy
                                RepRng.Offset(1, 0).PasteSpecial
                                Set RepRng = wsReport.Range("A" & Rows.Count).End(xlUp)
                            End If
                        Next
                        Application.CutCopyMode = False
                End Select 'IsDt for case when item is selected
        End Select 'ItemBox
End Select 'EmpList
And the Public Variables...thanks in advance!!
Option Explicit
Public IsDt As Boolean
Public wbInv As Workbook
Public MaintEmp, ItmLst, CheckOut, wsReport As Worksheet
Public EmpRange, Emp, CatRange, ItemRange, Cat, COCel, ItmBB, CCDate, CCEmp, CCItem, RepRng As Range
Public FromDate, ToDate As Date
Public Cel, ClearRange As Range
Sub PubVar()
    Set wbInv = ThisWorkbook
    Set MaintEmp = wbInv.Worksheets("Maintenance Employees")
    Set ItmLst = wbInv.Worksheets("Item List")
    Set CheckOut = wbInv.Worksheets("Check out List")
    Set wsReport = wbInv.Worksheets("Report")
    
    Set EmpRange = MaintEmp.Range("A2", MaintEmp.Range("A" & Rows.Count).End(xlUp))
    Set CatRange = ItmLst.Range("A2", ItmLst.Range("A" & Rows.Count).End(xlUp))
    Set ItemRange = ItmLst.Range("B2", ItmLst.Range("B" & Rows.Count).End(xlUp))
    Set COCel = CheckOut.Range("A" & Rows.Count).End(xlUp)
    Set CCDate = CheckOut.Range("A2", CheckOut.Range("A" & Rows.Count).End(xlUp))
    Set RepRng = wsReport.Range("A" & Rows.Count).End(xlUp)
    
    
End Sub