CopyNamesIntoNewWorksheet

'function used to extract all unique customers from a list based on their name
Function TransferUniqueCustomers()
    'variables
    Dim customers(1 To 500), name As String
    Dim i, CustIndex, SearchIndex As Long
    Dim wsData, wsCust As Worksheet

    'use the currently active workbook
    With ActiveWorkbook

    'initialize variables
    Set wsCust = Worksheet("Unique Customers")
    Set wsData = Worksheet("Job List (Fiscal 2015-2016)")
    CustIndex = SearchIndex = 1

    'for loop to collect all unique customer names
    For i = 1 To i = 500
        'create a variable for comparing the names and extract it from the correct column
        name = wsData.Cells(i, 6).Text

        'check for name against array
        For SearchIndex = 1 To SearchIndex = 500
            'check if customer's name is equal to any prior entry
            If name = customers(SearchIndex, 6) Then
                'equals another name in array so scrap search
                SearchIndex = 501
            ElseIf SearchIndex < 500 Then
                'continue searching
            Else
                'customer not found in array so add customer into array
                customers(CustIndex) = name
                CustIndex = CustIndex + 1
            End If
        Next
    'step forward in the loop
    Next

    'now that we have an accurate customer list enter data into column
    For i = 1 To i = CustIndex
        'load data into columns
        wsCust.Cells(i, 1) = customers(i)
    Next

    'update cell with correct number
    wsCust.Cells(2, 1) = "Unique customers: " & CustIndex

End Function