Excel Macro - Reformat Data to New Sheet

Sub MapData()
    'Declare variables
    Dim srcSheet As Worksheet
    Dim destSheet As Worksheet
    Dim srcLastRow As Long
    Dim destLastRow As Long
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim arrSourceColumns As Variant
    Dim arrDestColumns As Variant
    Dim arrUnknownColumns As Variant
    Dim arrDegreeTypes() As Variant
    Dim strPOINames As String
    Dim strPOIDescriptions As String
    Dim strPOIType As String
    Dim strPOIName As String
    Dim strPOIDescription As String
    Dim strDegreeType As Variant


    'Set variables
    Set srcSheet = Worksheets("Catalog")
    Set destSheet = Worksheets("vbiz-Catalog")
    srcLastRow = srcSheet.Cells(srcSheet.Rows.Count, 1).End(xlUp).Row
    destLastRow = destSheet.Cells(destSheet.Rows.Count, 1).End(xlUp).Row + 1
    arrSourceColumns = Split("DivisionID, Division Description, DisciplineID, Discipline Description, Division Type, POI ID, POI ID SF, POI Name (Marketing), UNKNOWN, Course Length, Has Pre Reqs, Total Courses Required, UNKNOWN, List Price, UNKNOWN, UNKNOWN", ", ")
    arrDestColumns = Split("DivisionID, DivisionDescription, DisciplineID, DisciplineDescription, POI_Type, POIID, POI_Code, POIName, Degree_Type, POILength(weeks), Individual Course (Y/N), TotalCoursesRequired, TotalCoursesAvailable, Tuition, MaterialFee, POI_Guide_URL, POI_Detail_Description", ", ")
    arrUnknownColumns = Split("UNKNOWN, UNKNOWN, UNKNOWN", ", ")
    arrDegreeTypes = Split("MS, MBA, MA, Master, Masters, Master’s", ", ")

    'Copy data
    For i = 2 To srcLastRow
        'Copy columns with known names
        For j = 0 To UBound(arrSourceColumns)
            destSheet.Cells(destLastRow, j + 1).Value = srcSheet.Cells(i, WorksheetFunction.Match(arrSourceColumns(j), srcSheet.Rows(1), 0)).Value
        Next j

        'Copy columns with unknown names
        For k = 0 To UBound(arrUnknownColumns)
            destSheet.Cells(destLastRow, UBound(arrSourceColumns) + 1 + k).Value = srcSheet.Cells(i, WorksheetFunction.Match(arrUnknownColumns(k), srcSheet.Rows(1), 0)).Value
        Next k

        'Check for POI Type
        strPOIType = srcSheet.Cells(i, WorksheetFunction.Match("POI Type", srcSheet.Rows(1), 0)).Value

        'Set POI Type in destination sheet
        If strPOIType Like "*Degree*" Then
            destSheet.Cells(destLastRow, WorksheetFunction.Match("POI_Type", destSheet.Rows(1), 0)).Value = "Degree"

            'Check POI Names and Descriptions for degree type keywords
            strPOINames = srcSheet.Cells(i, WorksheetFunction.Match("POI Name (Marketing)", srcSheet.Rows(1), 0)).Value
            strPOIDescriptions = srcSheet.Cells(i, WorksheetFunction.Match("POI Description", srcSheet.Rows(1), 0)).Value

            'Set degree type in destination sheet
For Each strDegreeType In arrDegreeTypes
    If InStr(strPOIType, "Degree") > 0 Then
        For Each strDegreeName In arrDegreeNames
            If InStr(strPOIName, strDegreeName) > 0 Or InStr(strPOIDesc, strDegreeName) > 0 Then
                wsDest.Cells(destRow, 9).Value = strDegreeType
                Exit For
            End If
        Next strDegreeName
    End If
Next strDegreeType


            'Set value for Degree_Type column based on POI_Type and POI Name/Description
            If Not IsEmpty(POIType) And Not IsEmpty(POIName) Then
                If InStr(1, POIType, "Degree", vbTextCompare) > 0 And _
                (InStr(1, POIName, "MS", vbTextCompare) > 0 Or _
                InStr(1, POIDesc, "MS", vbTextCompare) > 0 Or _
                InStr(1, POIName, "MBA", vbTextCompare) > 0 Or _
                InStr(1, POIDesc, "MBA", vbTextCompare) > 0 Or _
                InStr(1, POIName, "MA ", vbTextCompare) > 0 Or _
                InStr(1, POIDesc, "MA ", vbTextCompare) > 0 Or _
                InStr(1, POIName, "Master's", vbTextCompare) > 0 Or _
                InStr(1, POIDesc, "Master's", vbTextCompare) > 0 Or _
                InStr(1, POIName, "Masters", vbTextCompare) > 0 Or _
                InStr(1, POIDesc, "Masters", vbTextCompare) > 0) Then
                    wsDest.Cells(destRow, 9).Value = "Master’s"
                End If
            End If

            'Increment the destination row for the next record
            destRow = destRow + 1
        Next i
    End If
    Next strDivisionType
    MsgBox "Data has been successfully mapped.", vbInformation

End Sub