Option Explicit
Option Compare Text

'/ Workbooks
Public WbSubsheet                                   As Workbook     '/ Contains all Lumin Wealth submitted Business
Public WbAdviserReport                              As Workbook     '/ Will Contain an aggregation of the subsheet and a submission report (by month) for each adviser

'/ Adviser Report worksheets
Public WsAggregatedData                             As Worksheet    '/ Will contain the aggregated subsheet data
Public WsAdviserReport                              As Worksheet    '/ Will contain the submissions report, reported by Adviser
Public WsProviderReport                             As Worksheet    '/ Will contain the submissions report, reported by Provider
Public WsProductReport                              As Worksheet    '/ Will contain the submissions report, reportrd by Type of Business
Public WsChangedData                                As Worksheet    '/ Record of Data CleanUp

'/ Subsheet Worksheets
Public WsNewClient                                  As Worksheet    '/ 'New Client' Investments of Assets
Public WsExistingClient                             As Worksheet    '/ 'Existing Client' Investments of assets
Public WsGroupSchemes                               As Worksheet    '/ 'e.g. Corporate pension schemes and/or Auto Enrolment
Public WsOther                                      As Worksheet    '/ Misc. bits and pieces
Public WsMcOngoing                                  As Worksheet    '/ Martin's recurring product commissions e.g. insurance policies
Public WsJhOngoing                                  As Worksheet    '/ Jon's recurring product commissions e.g. insurance policies
Public WsAegonQuilterArc                            As Worksheet    '/ Recurring fees from accounts with Aegon, Quilter and ARC
Public WsAscentric                                  As Worksheet    '/ Recurring fees from accounts on Asccentric

'/ Data Arrays
Public ArrAggregatedArrays                          As Variant      '/ Holds all the sheet-Data Arrays

Public ArrAggregatedData                            As Variant      '/ The data from all worksheets

Public ArrProviders                                 As Variant      '/ all providers found in the subsheet
Public ArrAdvisers                                  As Variant      '/ all the advisers found in the subsheet

'/ Collections of names
Public ColAllHeadings                               As Collection   '/ All desired Column Headings from the subsheet
Public ColMetrics                                   As Collection   '/ Metrics in the final report
Public colAdviserNames                              As Collection   '/ All Adviser names that MIGHT be in the Subsheet

'/ Constants, and variables that are only set once
Public StrCurrentDate               As String           '/  The current Date for datestamping the saved report
Public StrSavedReportFilename       As String           '/  The filename to save the report as

Public LngFinalCellRow      As Long
Public LngFinalCellColumn   As Long

Public Const StrAdviserReportFilePath               As String = "S:Lumin Admin DocsAdviser Submission Reports"               '/  The path of the folder containing the Adviser Report
Public Const StrSavedReportsFilePath                As String = "S:Lumin Admin DocsAdviser Submission ReportsSaved Reports" '/  The path of the folder containing previous reports
Public Const StrSubsheetFilePath                    As String = "S:Lumin Admin DocsSubsheet and Commission statements"       '/  The path of the folder containing the Subsheet

Public Const StrAdviserReportFilename               As String = "Adviser Submissions Report - v0.5.xlsm"                        '/  The filename of the Adviser Submissions Report
Public Const StrSubsheetFilename                    As String = "Lumin Subsheet 2015.xlsm"                                      '/  The filename of the Subsheet

Public Const Hyphen As String = " - "

Public varScreenUpdating    As Boolean
Public varEnableEvents      As Boolean
Public varCalculation       As XlCalculation

Public Sub GenerateAdviserSubmissionReports()

    StoreApplicationSettings

    DisableApplicationSettings

    '/======================================================================================================================================================
    '/  Author:  Zak Armstrong
    '/  Email:   [email protected]
    '/  Date:    21/August/2015
    '/  Version: 0.3
    '/
    '/  Description:    All Lumin Wealth Business is contained in the Subsheet. This macro produces adviser totals for business (assets and fees) in the previous year
    '/                  (month by month breakdown) by aggregating the subsheet into one giant table and then assigning each piece of business to an adviser, a Month and a business type.
    '/                  The report can then be easily configured for any desired outputs (E.G. by adviser, by provider, by type of business)
    '/======================================================================================================================================================
    '/======================================================================================================================================================
        InitialiseGlobalsBooksSheetsAndCollections

        GetAllSheetDataIntoArrays

        FilterSheetArrays

        AggregateSheetData

        CloseWorkbook WbSubsheet

        PrintAggregatedData

        CleanUpAggregatedData

    RestoreApplicationSettings

End Sub

Public Sub InitialiseGlobalsBooksSheetsAndCollections()

        Sheets(1).Activate
        LngFinalCellRow = Sheets(1).Rows.Count
        LngFinalCellColumn = Sheets(1).Columns.Count

    '/ initialise public arrays
        ArrAggregatedData = Array()
        ArrAggregatedArrays = Array()
        ArrProviders = Array()
        ArrAdvisers = Array()

        GetWorkbook StrAdviserReportFilename, StrAdviserReportFilePath
        Set WbAdviserReport = Workbooks(StrAdviserReportFilename)

        GetWorkbook StrSubsheetFilename, StrSubsheetFilePath
        Set WbSubsheet = Workbooks(StrSubsheetFilename)

        AssignWorksheets

        InitialiseCollections

End Sub

Public Sub AssignWorksheets()

    '/======================================================================================================================================================
    '/  Date:           21.08.2015
    '/======================================================================================================================================================

        WbAdviserReport.Activate

            Set WsAggregatedData = WbAdviserReport.Worksheets("Aggregated Subsheet Data")
            Set WsAdviserReport = WbAdviserReport.Worksheets("Adviser Submissions Report")
            Set WsProviderReport = WbAdviserReport.Worksheets("Provider Submissions Report")
            Set WsProductReport = WbAdviserReport.Worksheets("Product Submissions Report")
            Set WsChangedData = WbAdviserReport.Worksheets("Changed Data")

        WbSubsheet.Activate

            Set WsNewClient = WbSubsheet.Worksheets("New Client Investment")
            Set WsExistingClient = WbSubsheet.Worksheets("Existing Client Investment")
            Set WsGroupSchemes = WbSubsheet.Worksheets("Group Schemes")
            Set WsOther = WbSubsheet.Worksheets("Other")
            Set WsMcOngoing = WbSubsheet.Worksheets("MC Ongoing")
            Set WsJhOngoing = WbSubsheet.Worksheets("JH Ongoing")
            Set WsAegonQuilterArc = WbSubsheet.Worksheets("AG-QU-ARC")
            Set WsAscentric = WbSubsheet.Worksheets("Ascentric")

End Sub

Public Sub InitialiseCollections()

    '/======================================================================================================================================================
    '/  Date:           21.08.2015
    '/======================================================================================================================================================
    Dim i As Long
    '/======================================================================================================================================================

        Set ColAllHeadings = New Collection
            '/ N.B. this will be the order of headings in the aggregated sheet

            ColAllHeadings.Add "Adviser"

            ColAllHeadings.Add "First Name"
            ColAllHeadings.Add "Last Name"
            ColAllHeadings.Add "Account Name"
            ColAllHeadings.Add "Life Co"
            ColAllHeadings.Add "Date Submitted"
            ColAllHeadings.Add "Description"

            ColAllHeadings.Add "Investment Amount"
            ColAllHeadings.Add "Money Received"

            ColAllHeadings.Add "Total Monthly Premium"
            ColAllHeadings.Add "Single Premium"

            ColAllHeadings.Add "Commission Due"
            ColAllHeadings.Add "Comm Paid - Checked To Bank"
            ColAllHeadings.Add "Date Received - Bank"

            For i = 1 To 12
                ColAllHeadings.Add DateValue("01/" & Right("0" & i, 2) & "/2015")
            Next i


        Set ColMetrics = New Collection
            ColMetrics.Add "Investment Amount"
            ColMetrics.Add "Single Premium"
            ColMetrics.Add "Total Monthly Premium"
            ColMetrics.Add "Commission Due"
            ColMetrics.Add "Comm Paid - Checked To Bank"
            ColMetrics.Add "Recurring"


        Set colAdviserNames = New Collection
            colAdviserNames.Add "Martin Cotter", "Martin"
            colAdviserNames.Add "Jon Hussey", "Jon"
            colAdviserNames.Add "Micky Mahbubani", "Micky"
            colAdviserNames.Add "Jeremy Smith", "Jeremy"
            colAdviserNames.Add "Sarah Cotter", "Sarah"
            colAdviserNames.Add "John Cusins", "John"

End Sub

Private Sub GetAllSheetDataIntoArrays()

    '/======================================================================================================================================================
    '/  Author:  Zak Armstrong
    '/  Email:   [email protected]
    '/  Date:    28/August/2015
    '/
    '/  Description:    Creates Arrays for each sheet, Calls sub to fill each with their sheet's data, collects arrays together in arrAggregatedArrys
    '/======================================================================================================================================================
    Dim arrNewClient                        As Variant
        arrNewClient = Array()

    Dim arrExistingClient                   As Variant
        arrExistingClient = Array()

    Dim arrGroupSchemes                     As Variant
        arrGroupSchemes = Array()

    Dim arrOther                            As Variant
        arrOther = Array()

    Dim arrMcOngoing                        As Variant
        arrMcOngoing = Array()

    Dim arrJhOngoing                        As Variant
        arrJhOngoing = Array()

    Dim arrAegonQuilterArc                  As Variant
        arrAegonQuilterArc = Array()

    Dim arrAscentric                        As Variant
        arrAscentric = Array()
    '/======================================================================================================================================================

        Dim strTopLeftCellIdentifier    As String
            strTopLeftCellIdentifier = "Adviser"

            PutSheetDataInArray WbSubsheet, WsNewClient, arrNewClient, strTopLeftCellIdentifier
            PutSheetDataInArray WbSubsheet, WsExistingClient, arrExistingClient, strTopLeftCellIdentifier
            PutSheetDataInArray WbSubsheet, WsGroupSchemes, arrGroupSchemes, strTopLeftCellIdentifier
            PutSheetDataInArray WbSubsheet, WsOther, arrOther, strTopLeftCellIdentifier
            PutSheetDataInArray WbSubsheet, WsMcOngoing, arrMcOngoing, strTopLeftCellIdentifier
            PutSheetDataInArray WbSubsheet, WsJhOngoing, arrJhOngoing, strTopLeftCellIdentifier
            PutSheetDataInArray WbSubsheet, WsAegonQuilterArc, arrAegonQuilterArc, strTopLeftCellIdentifier

        strTopLeftCellIdentifier = "Account No"
            PutSheetDataInArray WbSubsheet, WsAscentric, arrAscentric, strTopLeftCellIdentifier

            InsertAscentricLifeCoColumn arrAscentric

        ReDim ArrAggregatedArrays(1 To 8)
            ArrAggregatedArrays(1) = arrNewClient
            ArrAggregatedArrays(2) = arrExistingClient
            ArrAggregatedArrays(3) = arrGroupSchemes
            ArrAggregatedArrays(4) = arrOther
            ArrAggregatedArrays(5) = arrMcOngoing
            ArrAggregatedArrays(6) = arrJhOngoing
            ArrAggregatedArrays(7) = arrAegonQuilterArc
            ArrAggregatedArrays(8) = arrAscentric

End Sub

Private Sub FilterSheetArrays()

    Dim i As Long
    Dim LB1 As Long, UB1 As Long

        AssignArrayBounds ArrAggregatedArrays, LB1, UB1

        For i = LB1 To UB1
            FilterSheetArrayForColumns ArrAggregatedArrays(i)
        Next i

End Sub

Private Sub FilterSheetArrayForColumns(ByRef arrSource As Variant)

    '/======================================================================================================================================================
    '/  Author:  Zak Armstrong
    '/  Email:   [email protected]
    '/  Date:    12/August/2015
    '/
    '/  Description:    Takes Sheet arrays, finds the columns from the colAllHeadings, recreates the array with just that data (and empty columns for the ones not found)
    '/======================================================================================================================================================
    Dim i As Long, j As Long, k As Long

    Dim LB1 As Long, UB1 As Long
    Dim LB2 As Long, UB2 As Long

    Dim arrTempArray        As Variant
        arrTempArray = Array()

    Dim arrHeadingsRow      As Variant
        arrHeadingsRow = Array()
    '/======================================================================================================================================================

        AssignArrayBounds arrSource, LB1, UB1, LB2, UB2

        arrHeadingsRow = RowFrom2dArray(arrSource, 1)

        arrHeadingsRow = ElementsToStrings1dArray(arrHeadingsRow)

    ReDim arrTempArray(0 To UB1, 0 To ColAllHeadings.Count)
        arrTempArray(0, 0) = arrSource(0, 0)


    Dim lngDestinationColumn    As Long
    Dim lngSourceColumn         As Long

    Dim varColumnPosition   As Variant

    Dim strHeading  As String

        For i = 1 To ColAllHeadings.Count
            strHeading = ColAllHeadings(i)
            varColumnPosition = IndexInArray1d(arrHeadingsRow, strHeading)

                If IsError(varColumnPosition) _
                    Then
                        MissingDataHeadingsHandler arrSource, strHeading
                    Else
                        lngDestinationColumn = i
                        lngSourceColumn = varColumnPosition

                        CopyArrayColumn2d arrSource, lngSourceColumn, arrTempArray, lngDestinationColumn
                End If
        Next i

        arrSource = arrTempArray

End Sub

Public Sub MissingDataHeadingsHandler(ByRef arrCurrentArray As Variant, ByVal strHeading As String)

    '/======================================================================================================================================================
    '/  Author:  Zak Armstrong
    '/  Email:   [email protected]
    '/  Date:    13/August/2015
    '/
    '/  Description:    Handle instances where a column heading can't be found. Reference against sheet-specific lists to see if the column should be there or not.
    '/======================================================================================================================================================
    Dim bErrorFound                 As Boolean

    Dim colMissingSheetHeadings     As Collection   '/  For each sheet, contains the headings that shouldn't be there

    Dim strException                As String       '/  holds string items from colMissingSheetHeadings
    Dim strErrorMessage             As String

    Dim i                           As Long
    Dim j                           As Long
    Dim k                           As Long
    '/======================================================================================================================================================

        strErrorMessage = "Couldn't find Column Heading: " & arrCurrentArray(0, 0) & ": " & strHeading
        bErrorFound = True

        Set colMissingSheetHeadings = New Collection

            Select Case arrCurrentArray(0, 0) '/ Contains the name of the worksheet the data was taken from

                Case Is = WsNewClient.Name
                    InitialiseNewClientHeadingsExceptions colMissingSheetHeadings

                Case Is = WsExistingClient.Name
                    InitialiseExistingClientHeadingsExceptions colMissingSheetHeadings


                Case Is = WsGroupSchemes.Name
                    InitialiseGroupSchemesHeadingsExceptions colMissingSheetHeadings


                Case Is = WsOther.Name
                    InitialiseOtherHeadingsExceptions colMissingSheetHeadings


                Case Is = WsMcOngoing.Name
                    InitialiseMcOngoingHeadingsExceptions colMissingSheetHeadings


                Case Is = WsJhOngoing.Name
                    InitialiseJhOngoingHeadingsExceptions colMissingSheetHeadings


                Case Is = WsAegonQuilterArc.Name
                    InitialiseAegonQuilterArcHeadingsExceptions colMissingSheetHeadings


                Case Is = WsAscentric.Name
                    InitialiseAscentricHeadingsExceptions colMissingSheetHeadings

                Case Else
                    ErrorMessage strErrorMessage

            End Select


                For i = 1 To colMissingSheetHeadings.Count
                    strException = colMissingSheetHeadings(i)
                    If strHeading = strException Then bErrorFound = False
                Next i

                If bErrorFound = True Then ErrorMessage (strErrorMessage)

End Sub

Public Sub InitialiseNewClientHeadingsExceptions(ByRef colMissingSheetHeadings As Collection)

    Dim i As Long

            colMissingSheetHeadings.Add ("Total Monthly Premium")
            colMissingSheetHeadings.Add ("Single Premium")
            colMissingSheetHeadings.Add ("Account Name")
            colMissingSheetHeadings.Add ("Life Co")
        For i = 1 To 12
            colMissingSheetHeadings.Add (DateValue("01/" & Right("0" & i, 2) & "/" & Year(Date)))
        Next i

End Sub

Private Sub AggregateSheetData()

    '/======================================================================================================================================================
    '/  Author:  Zak Armstrong
    '/  Email:   [email protected]
    '/  Date:    13/August/2015
    '/
    '/  Description:   For Each array, add the data to arrAggregatedData
    '/======================================================================================================================================================
    Dim i As Long, j As Long, k As Long

    Dim rngTopLeftCell  As Range

    Dim lngCurrentRow   As Long

    Dim LB1 As Long, UB1 As Long
    Dim LB2 As Long, UB2 As Long
    '/======================================================================================================================================================
        ReDim ArrAggregatedData(1 To ColAllHeadings.Count, 1 To 1)

            lngCurrentRow = 1

            For i = 1 To ColAllHeadings.Count
                ArrAggregatedData(i, 1) = ColAllHeadings(i)
            Next i

        '/ All arrays were created as 0 - X, 0 - Y, hence LB + 1 and LB + 2
            For i = LBound(ArrAggregatedArrays) To UBound(ArrAggregatedArrays)

                AssignArrayBounds ArrAggregatedArrays(i), LB1, UB1, LB2, UB2

                    For j = LB1 + 2 To UB1
                    lngCurrentRow = lngCurrentRow + 1
                    ReDim Preserve ArrAggregatedData(1 To ColAllHeadings.Count, 1 To lngCurrentRow)

                        For k = LB2 + 1 To UB2
                            ArrAggregatedData(k, lngCurrentRow) = ArrAggregatedArrays(i)(j, k)
                        Next k
                    Next j
            Next i

        Transpose2dArray ArrAggregatedData

End Sub

Private Sub FilterSheetArrays()

    Dim i As Long
    Dim LB1 As Long, UB1 As Long

        AssignArrayBounds ArrAggregatedArrays, LB1, UB1

        For i = LB1 To UB1
            FilterSheetArrayForColumns ArrAggregatedArrays(i)
        Next i

End Sub

Private Sub CleanUpAggregatedData()

    '/======================================================================================================================================================
    '/  Author:  Zak Armstrong
    '/  Email:   [email protected]
    '/  Date:    13/August/2015
    '/
    '/  Description:   Clean up the aggregated data table (converting shortened names to full names, removing in-sheet totals, replacing "N/A" etc.)
    '/                 Makes a record of all changes (with the row for context) in the "Changed Data" sheet.
    '/======================================================================================================================================================
    Dim lngHeaderEndColumn As Long

    Dim LB1 As Long, UB1 As Long
    Dim LB2 As Long, UB2 As Long

    Dim arrChangedData As Variant
        arrChangedData = Array()
    '/======================================================================================================================================================

            CreateHeadingChangedData arrChangedData, lngHeaderEndColumn

        AssignRangeBoundsOfData WsAggregatedData.Cells(1, 1), LB1, UB1, LB2, UB2

            RemoveUnwantedData arrChangedData, lngHeaderEndColumn, LB1, UB1, LB2, UB2

            Transpose2dArray arrChangedData

            Print2dArrayToSheet WbAdviserReport, WsChangedData, arrChangedData, WsChangedData.Cells(1, 1)

        AssignRangeBoundsOfData WsAggregatedData.Cells(1, 1), LB1, UB1, LB2, UB2

            ChangeAdviserNames lngHeaderEndColumn, LB1, UB1, LB2, UB2

End Sub

Public Sub CreateHeadingChangedData(ByRef arrChangedData As Variant, ByRef lngHeaderEndColumn As Long)

Dim i As Long

    ReDim arrChangedData(1 To ColAllHeadings.Count + 4, 1 To 1)

        arrChangedData(1, 1) = "Trigger Value"
        arrChangedData(2, 1) = "Row"
        arrChangedData(3, 1) = "Action"
        lngHeaderEndColumn = 3 + 1

        For i = 1 To ColAllHeadings.Count
            arrChangedData(lngHeaderEndColumn + i, 1) = ColAllHeadings(i)
        Next i

End Sub

Public Sub RemoveUnwantedData(ByRef arrChangedData As Variant, ByRef lngHeaderEndColumn As Long, ByRef LB1 As Long, ByRef UB1 As Long, ByRef LB2 As Long, ByRef UB2 As Long)

    Dim rngHolder As Range
    Dim i As Long, j As Long

    WbAdviserReport.Activate
    WsAggregatedData.Activate

        For i = UB1 To LB1 + 1 Step -1

            Set rngHolder = Cells(i, LB2)
            If rngHolder.Text = "Total" Then RemoveRow arrChangedData, lngHeaderEndColumn, rngHolder, LB2, UB2

            '/ Numeric Columns: (1) + 5 (Date Submitted) (1) + (7 - 25) (Inv. amount, premiums, commissions, Jan 2015 - Dec 2015)
            Set rngHolder = Cells(i, LB2 + 5)
            If Not (IsNumeric(rngHolder.Value) Or IsDate(rngHolder.Value)) Then RemoveCellContents arrChangedData, lngHeaderEndColumn, rngHolder, LB2, UB2

            For j = 7 To 25
                Set rngHolder = Cells(i, LB2 + j)
                If Not (IsNumeric(rngHolder.Value) Or IsDate(rngHolder.Value)) Then RemoveCellContents arrChangedData, lngHeaderEndColumn, rngHolder, LB2, UB2
            Next j

        Next i

End Sub

Public Sub RemoveCellContents(ByRef arrChangedData As Variant, ByVal lngHeaderEndColumn As Long, ByRef rngTargetCell As Range, ByVal lngFirstColumn As Long, ByVal lngFinalColumn As Long)

    Dim lngCurrentRow As Long
    Dim lngFinalRow   As Long

    Dim lngRowLength As Long
        lngRowLength = lngFinalColumn - lngFirstColumn + 1

    Dim rngTargetRow As Range

    Dim i As Long

    Dim arrTemp() As Variant
    ReDim arrTemp(1 To lngRowLength) As Variant

        lngCurrentRow = rngTargetCell.Row
        For i = lngFirstColumn To lngFinalColumn
            arrTemp(i) = WsAggregatedData.Cells(lngCurrentRow, i).Value
        Next i

    Dim LB1 As Long, UB1 As Long
    Dim LB2 As Long, UB2 As Long

        AssignArrayBounds arrChangedData, LB1, UB1, LB2, UB2
    ReDim Preserve arrChangedData(LB1 To UB1, LB2 To UB2 + 1)

        For i = 1 To lngRowLength
            arrChangedData(lngHeaderEndColumn + i, UB2 + 1) = arrTemp(i)
        Next i

        arrChangedData(1, UB2 + 1) = rngTargetCell.Value
        arrChangedData(2, UB2 + 1) = "Row: " & rngTargetCell.Row
        arrChangedData(3, UB2 + 1) = "Cleared Contents"

        rngTargetCell.ClearContents

End Sub

Public Sub RemoveRow(ByRef arrChangedData As Variant, ByVal lngHeaderEndColumn As Long, ByRef rngTargetCell As Range, ByVal lngFirstColumn As Long, ByVal lngFinalColumn As Long)

    Dim lngCurrentRow As Long
    Dim lngFinalRow   As Long

    Dim lngRowLength As Long
        lngRowLength = lngFinalColumn - lngFirstColumn + 1

    Dim rngTargetRow As Range

    Dim i As Long

    Dim arrTemp() As Variant
    ReDim arrTemp(1 To lngRowLength) As Variant

        lngCurrentRow = rngTargetCell.Row
        For i = lngFirstColumn To lngFinalColumn
            arrTemp(i) = WsAggregatedData.Cells(lngCurrentRow, i).Value
        Next i

    Dim LB1 As Long, UB1 As Long
    Dim LB2 As Long, UB2 As Long

        AssignArrayBounds arrChangedData, LB1, UB1, LB2, UB2
    ReDim Preserve arrChangedData(LB1 To UB1, LB2 To UB2 + 1)

        For i = 1 To lngRowLength
            arrChangedData(lngHeaderEndColumn + i, UB2 + 1) = arrTemp(i)
        Next i

        arrChangedData(1, UB2 + 1) = rngTargetCell.Value
        arrChangedData(2, UB2 + 1) = "Row: " & rngTargetCell.Row
        arrChangedData(3, UB2 + 1) = "Deleted Row"

        Rows(lngCurrentRow).Delete

End Sub

Public Sub ChangeAdviserNames(ByRef lngHeaderEndColumn As Long, ByRef LB1 As Long, ByRef UB1 As Long, ByRef LB2 As Long, ByRef UB2 As Long)

    WbAdviserReport.Activate
    WsAggregatedData.Activate

    Dim rngHolder As Range

    Dim i As Long, j As Long

    Dim bError As Boolean

    Dim strErrorMessage As String

        For i = UB1 To LB1 + 1 Step -1

        Set rngHolder = Cells(i, LB2)

            Select Case rngHolder.Text

                Case Is = "Jon"
                    rngHolder.Value = colAdviserNames.Item(rngHolder.Text)

                Case Is = "Martin"
                    rngHolder.Value = colAdviserNames.Item(rngHolder.Text)

                Case Is = "Micky"
                    rngHolder.Value = colAdviserNames.Item(rngHolder.Text)

                Case Is = "Jeremy"
                    rngHolder.Value = colAdviserNames.Item(rngHolder.Text)

                Case Is = "John"
                    rngHolder.Value = colAdviserNames.Item(rngHolder.Text)

                Case Is = "Sarah"
                    rngHolder.Value = colAdviserNames.Item(rngHolder.Text)

                Case Else
                    bError = True

                    For j = 1 To colAdviserNames.Count
                        If rngHolder.Text = colAdviserNames(j) Then bError = False
                    Next j

                    If bError _
                        Then
                            strErrorMessage = "Unidentified Adviser - Row: " & i & "Text: " & rngHolder.Text
                            ErrorMessage (strErrorMessage)
                    End If

            End Select

        Next i

End Sub

Option Explicit
Option Compare Text

Public Sub GetWorkbook(ByVal strFilename As String, ByVal strFilePath As String)

    Dim bWbIsOpen As Boolean

    bWbIsOpen = WorkbookIsOpen(strFilename)
    If Not bWbIsOpen Then Workbooks.Open strFilePath & strFilename

End Sub

Public Function WorkbookIsOpen(ByVal strTargetName As String) As Boolean

    Dim wbTest As Workbook

        On Error Resume Next

            Set wbTest = Workbooks(strTargetName)

            WorkbookIsOpen = (wbTest.Name = strTargetName)

        On Error GoTo 0

End Function

Public Sub PutSheetDataInArray(ByRef wbCurrent As Workbook, ByRef wsCurrent As Worksheet, ByRef arrCurrentArray As Variant, Optional ByVal strTopLeftCellIdentifier As Variant, _
Optional ByVal lngStartRow As Long = 1, Optional ByVal lngEndRow As Variant, _
Optional ByVal lngStartColumn As Long = 1, Optional ByVal lngEndColumn As Variant)
    '/======================================================================================================================================================
    Dim i As Long, j As Long, k As Long

    Dim rngTopLeftCell  As Range
    Dim rngSearchRange  As Range

    Dim strErrorMessage As String

    Dim arrHiddenColumns    As Variant
        arrHiddenColumns = Array()

    Dim arrHiddenRows       As Variant
        arrHiddenRows = Array()

    Dim LB1 As Long, UB1 As Long
    Dim LB2 As Long, UB2 As Long
    '/======================================================================================================================================================
        wbCurrent.Activate
        wsCurrent.Activate

        If IsMissing(strTopLeftCellIdentifier) _
            Then
                Set rngTopLeftCell = Cells(1, 1)
            ElseIf TypeName(strTopLeftCellIdentifier) = "String" _
                Then
                    If IsMissing(lngEndRow) Then lngEndRow = wsCurrent.Rows.Count
                    If IsMissing(lngEndColumn) Then lngEndColumn = wsCurrent.Columns.Count

                    Set rngSearchRange = wsCurrent.Range(Cells(lngStartRow, lngStartColumn), Cells(lngEndRow, lngEndColumn))
                    Set rngTopLeftCell = CellContainingStringInRange(rngSearchRange, strTopLeftCellIdentifier)
                Else
                    strErrorMessage = "strTopLeftCellIdentifier must be a string, not a " & TypeName(strTopLeftCellIdentifier)
                    ErrorMessage (strErrorMessage)
        End If


        LB1 = rngTopLeftCell.Row
        LB2 = rngTopLeftCell.Column
        AssignRangeBoundsOfData rngTopLeftCell, UB1:=UB1, UB2:=UB2

        RecordHiddenRowsAndUnhide arrHiddenRows, LB1, UB1
        RecordHiddenColumnsAndUnhide arrHiddenColumns, LB2, UB2

            WriteRangeToArrayIteratively wsCurrent, arrCurrentArray, LB1, UB1, LB2, UB2

        HideRows arrHiddenRows
        HideColumns arrHiddenColumns

End Sub

Public Function CellContainingStringInRange(ByRef rngSearch As Range, ByVal strSearch As String) As Range

    Dim strErrorMessage As String

        Set CellContainingStringInRange = rngSearch.Find(strSearch, LookIn:=xlValues)

        If CellContainingStringInRange Is Nothing _
            Then
                strErrorMessage = "Couldn't find cell """ & strSearch & """ in " & rngSearch.Worksheet.Name
                ErrorMessage (strErrorMessage)
        End If

End Function

Public Sub RecordHiddenRowsAndUnhide(ByRef arrHiddenRows As Variant, ByVal LB1 As Long, ByVal UB1 As Long)

    Dim i As Long

    Dim lngCounter As Long

        For i = LB1 To UB1
            If Rows(i).EntireRow.Hidden _
                Then
                    lngCounter = lngCounter + 1
                    ReDim Preserve arrHiddenRows(1 To lngCounter)
                    arrHiddenRows(lngCounter) = i
                    Rows(i).Hidden = False
            End If
        Next i

End Sub

Public Sub RecordHiddenColumnsAndUnhide(ByRef arrHiddenColumns As Variant, ByVal LB2 As Long, ByVal UB2 As Long)

    Dim i As Long

    Dim lngCounter As Long

        For i = LB2 To UB2
            If Columns(i).EntireRow.Hidden _
                Then
                    lngCounter = lngCounter + 1
                    ReDim Preserve arrHiddenColumns(1 To lngCounter)
                    arrHiddenColumns(lngCounter) = i
                    Columns(i).Hidden = False
            End If
        Next i

End Sub

Public Sub HideRows(ByRef arrHiddenRows As Variant)
    Dim i As Long

        For i = LBound(arrHiddenRows) To UBound(arrHiddenRows)
            Rows(i).EntireRow.Hidden = True
        Next i

End Sub

Public Sub HideColumns(ByRef arrHiddenColumns As Variant)
    Dim i As Long

        For i = LBound(arrHiddenColumns) To UBound(arrHiddenColumns)
            Columns(i).EntireRow.Hidden = True
        Next i

End Sub

Public Sub AssignRangeBoundsOfData(ByRef rngCell As Range, Optional ByRef LB1 As Variant, Optional ByRef UB1 As Variant, Optional ByRef LB2 As Variant, Optional ByRef UB2 As Variant)

    Dim wbCurrent As Workbook
    Dim wsCurrent As Worksheet

        AssignCurrentBookAndSheet wbCurrent, wsCurrent

    Dim wsRngCell As Worksheet
    Dim wbRngCell As Workbook

        AssignRangeBookAndSheet rngCell, wbRngCell, wsRngCell

        wbRngCell.Activate
        wsRngCell.Activate

    Dim rngCurrentRegion As Range
    Set rngCurrentRegion = rngCell.CurrentRegion

        If Not IsMissing(LB1) Then LB1 = rngCurrentRegion.Row
        If Not IsMissing(LB2) Then LB2 = rngCurrentRegion.Column

        If Not IsMissing(UB1) Then UB1 = rngCurrentRegion.Row + rngCurrentRegion.Rows.Count - 1
        If Not IsMissing(UB2) Then UB2 = rngCurrentRegion.Column + rngCurrentRegion.Columns.Count - 1

        wbCurrent.Activate
        wsCurrent.Activate

End Sub

Public Sub CopyArrayContents5d(ByRef arrSource As Variant, ByRef arrDestination As Variant)

    Dim LB1 As Long, UB1 As Long
    Dim LB2 As Long, UB2 As Long
    Dim LB3 As Long, UB3 As Long
    Dim LB4 As Long, UB4 As Long
    Dim LB5 As Long, UB5 As Long

    Dim i As Long, j As Long, k As Long
    Dim l As Long, m As Long

        AssignArrayBounds arrSource, LB1, UB1, LB2, UB2, LB3, UB3, LB4, UB4, LB5, UB5

        Erase arrDestination
        ReDim arrDestination(LB1 To UB1, LB2 To UB2, LB3 To UB3, LB4 To UB4, LB5 To UB5)

        For i = LB1 To UB1
        For j = LB2 To UB2
        For k = LB3 To UB3
        For l = LB4 To UB4
        For m = LB5 To UB5
            arrDestination(i, j, k, l, m) = arrSource(i, j, k, l, m)
        Next m
        Next l
        Next k
        Next j
        Next i

End Sub

Public Sub CopyArrayContents4d(ByRef arrSource As Variant, ByRef arrDestination As Variant)

    Dim LB1 As Long, UB1 As Long
    Dim LB2 As Long, UB2 As Long
    Dim LB3 As Long, UB3 As Long
    Dim LB4 As Long, UB4 As Long

    Dim i As Long, j As Long, k As Long
    Dim l As Long

        AssignArrayBounds arrSource, LB1, UB1, LB2, UB2, LB3, UB3, LB4, UB4

        Erase arrDestination
        ReDim arrDestination(LB1 To UB1, LB2 To UB2, LB3 To UB3, LB4 To UB4)

        For i = LB1 To UB1
        For j = LB2 To UB2
        For k = LB3 To UB3
        For l = LB4 To UB4
            arrDestination(i, j, k, l) = arrSource(i, j, k, l)
        Next l
        Next k
        Next j
        Next i

End Sub

Public Sub CopyArrayContents3d(ByRef arrSource As Variant, ByRef arrDestination As Variant)

    Dim LB1 As Long, UB1 As Long
    Dim LB2 As Long, UB2 As Long
    Dim LB3 As Long, UB3 As Long

    Dim i As Long, j As Long, k As Long

        AssignArrayBounds arrSource, LB1, UB1, LB2, UB2, LB3, UB3

        Erase arrDestination
        ReDim arrDestination(LB1 To UB1, LB2 To UB2, LB3 To UB3)

        For i = LB1 To UB1
        For j = LB2 To UB2
        For k = LB3 To UB3
            arrDestination(i, j, k) = arrSource(i, j, k)
        Next k
        Next j
        Next i

End Sub

Public Sub CopyArrayContents2d(ByRef arrSource As Variant, ByRef arrDestination As Variant)

    Dim LB1 As Long, UB1 As Long
    Dim LB2 As Long, UB2 As Long

    Dim i As Long, j As Long

        AssignArrayBounds arrSource, LB1, UB1, LB2, UB2

        Erase arrDestination
        ReDim arrDestination(LB1 To UB1, LB2 To UB2)

        For i = LB1 To UB1
        For j = LB2 To UB2
            arrDestination(i, j) = arrSource(i, j)
        Next j
        Next i

End Sub

Public Sub CopyArrayContents1d(ByRef arrSource As Variant, ByRef arrDestination As Variant)

    Dim LB1 As Long, UB1 As Long

    Dim i As Long

        AssignArrayBounds arrSource, LB1, UB1

        Erase arrDestination
        ReDim arrDestination(LB1 To UB1)

        For i = LB1 To UB1
            arrDestination(i) = arrSource(i)
        Next i

End Sub

Public Sub AssignArrayBounds(ByRef arrCurrentArray As Variant, _
                                Optional ByRef LB1 As Variant, Optional ByRef UB1 As Variant, _
                                Optional ByRef LB2 As Variant, Optional ByRef UB2 As Variant, _
                                Optional ByRef LB3 As Variant, Optional ByRef UB3 As Variant, _
                                Optional ByRef LB4 As Variant, Optional ByRef UB4 As Variant, _
                                Optional ByRef LB5 As Variant, Optional ByRef UB5 As Variant)

    If Not IsMissing(LB1) Then LB1 = LBound(arrCurrentArray, 1)
    If Not IsMissing(UB1) Then UB1 = UBound(arrCurrentArray, 1)
    If Not IsMissing(LB2) Then LB2 = LBound(arrCurrentArray, 2)
    If Not IsMissing(UB2) Then UB2 = UBound(arrCurrentArray, 2)
    If Not IsMissing(LB3) Then LB3 = LBound(arrCurrentArray, 3)
    If Not IsMissing(UB3) Then UB3 = UBound(arrCurrentArray, 3)
    If Not IsMissing(LB4) Then LB4 = LBound(arrCurrentArray, 4)
    If Not IsMissing(UB4) Then UB4 = UBound(arrCurrentArray, 4)
    If Not IsMissing(LB5) Then LB5 = LBound(arrCurrentArray, 5)
    If Not IsMissing(UB5) Then UB5 = UBound(arrCurrentArray, 5)

End Sub

Public Sub Transpose2dArray(ByRef arrCurrentArray As Variant)

    Dim LB1 As Long, UB1 As Long
    Dim LB2 As Long, UB2 As Long

    Dim i As Long, j As Long

        AssignArrayBounds arrCurrentArray, LB1, UB1, LB2, UB2

    Dim arrTransposedArray() As Variant
    ReDim arrTransposedArray(LB2 To UB2, LB1 To UB1)

        For i = LB1 To UB1
        For j = LB2 To UB2
            arrTransposedArray(j, i) = arrCurrentArray(i, j)
        Next j
        Next i

    Erase arrCurrentArray
    ReDim arrCurrentArray(LB2 To UB2, LB1 To UB1)

        arrCurrentArray = arrTransposedArray

End Sub

Public Sub Print2dArrayToSheet(ByRef wbTarget As Workbook, ByRef wsTarget As Worksheet, ByRef arrData As Variant, ByRef rngStartCell As Range)

    Dim LB1 As Long, UB1 As Long
    Dim LB2 As Long, UB2 As Long

    Dim rngTableRange As Range

        wbTarget.Activate
        wsTarget.Activate

        AssignArrayBounds arrData, LB1, UB1, LB2, UB2
    Set rngTableRange = Range(rngStartCell, Cells(rngStartCell.Row + UB1 - LB1, rngStartCell.Column + UB2 - LB2))
        rngTableRange = arrData

End Sub

Public Sub CopyArrayColumn2d(ByRef arrSource As Variant, ByVal lngSourceColumn As Long, ByRef arrTarget As Variant, ByVal lngTargetColumn As Long)

    Dim i As Long, j As Long, k As Long

    Dim LB1 As Long, UB1 As Long

        AssignArrayBounds arrSource, LB1, UB1

        For i = LB1 To UB1
            arrTarget(i, lngTargetColumn) = arrSource(i, lngSourceColumn)
        Next i

End Sub

Public Function RowFrom2dArray(ByRef arrSource As Variant, ByVal lngRow As Long) As Variant

    Dim LB2 As Long, UB2 As Long
    Dim i As Long

    Dim arrRow As Variant
        arrRow = Array()

        AssignArrayBounds arrSource, LB2:=LB2, UB2:=UB2

        ReDim arrRow(LB2 To UB2)

        For i = LB2 To UB2
            arrRow(i) = arrSource(lngRow, i)
        Next i

        RowFrom2dArray = arrRow

End Function

Public Function IndexInArray1d(ByRef arrSource As Variant, ByVal varSearch As Variant) As Variant

    Dim LB1 As Long, UB1 As Long

    Dim bMatchFound As Boolean

    Dim i As Long

        AssignArrayBounds arrSource, LB1, UB1
        bMatchFound = False

        i = LB1
        Do While i <= UB1 And bMatchFound = False
            If arrSource(i) = varSearch _
                Then
                    bMatchFound = True
                    IndexInArray1d = i
            End If
            i = i + 1
        Loop

        If Not bMatchFound Then IndexInArray1d = CVErr(xlErrValue)

End Function

Public Sub AssignCurrentBookAndSheet(ByRef wbCurrent As Workbook, ByRef wsCurrent As Worksheet)

    Set wbCurrent = ThisWorkbook
    Set wsCurrent = ActiveSheet

End Sub

Public Sub AssignRangeBookAndSheet(ByRef rngTarget As Range, ByRef wbTarget As Workbook, ByRef wsTarget As Worksheet)

    Set wbTarget = rngTarget.Worksheet.Parent
    Set wsTarget = rngTarget.Worksheet

End Sub

Public Sub WriteRangeToArrayIteratively(ByRef wsCurrent As Worksheet, arrCurrentArray As Variant, ByVal LB1 As Long, ByVal UB1 As Long, ByVal LB2 As Long, ByVal UB2 As Long)
    Dim i As Long, j As Long

    wsCurrent.Activate

    ReDim arrCurrentArray(0 To UB1 - LB1 + 1, 0 To UB2 - LB2 + 1)
        arrCurrentArray(0, 0) = wsCurrent.Name

        For i = LB1 To UB1
            For j = LB2 To UB2
                arrCurrentArray(i - LB1 + 1, j - LB2 + 1) = wsCurrent.Cells(i, j)
            Next j
        Next i

End Sub

Public Function ElementsToStrings1dArray(ByRef arrSource As Variant) As Variant

    Dim i As Long
    Dim arrRow As Variant
        arrRow = arrSource

        For i = LBound(arrSource) To UBound(arrSource)
            arrRow(i) = CStr(arrRow(i))
        Next i

        ElementsToStrings1dArray = arrRow

End Function

Public Sub ErrorMessage(ByVal strErrorMessage As String)

    MsgBox strErrorMessage
    Debug.Print strErrorMessage
    RestoreApplicationSettings
    Stop

End Sub

Public Sub StoreApplicationSettings()

    varScreenUpdating = Application.ScreenUpdating
    varEnableEvents = Application.EnableEvents
    varCalculation = Application.Calculation

End Sub

Public Sub DisableApplicationSettings()

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

End Sub

Public Sub RestoreApplicationSettings()

    Application.ScreenUpdating = varScreenUpdating
    Application.EnableEvents = varEnableEvents
    Application.Calculation = varCalculation

End Sub

Public Sub CloseWorkbook(ByRef wbTarget As Workbook)

    Application.DisplayAlerts = False
        wbTarget.Close
    Application.DisplayAlerts = True

End Sub