Pulling File Name in Excel VBA

Sub Format_Small_Funds()




'Turn on Optimize_VBA
    'Optimize_VBA True
    
'Error handling
On Error GoTo Error_Handler


Dim lastRow         As Integer
Dim NameOfWorkbook  As String


NameOfWorkbook = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".", -1, vbTextCompare) - 1))
sNameOfWorkbook = Left(NameOfWorkbook, 9)
    Debug.Print sNameOfWorkbook
    
'Make sure you have saved this workbook as an xlsm file
iResponse = MsgBox(" Have you saved this file with the .XLSM extension? ", vbQuestion + vbYesNo, " Quick Question ")
                    
If iResponse = vbYes Then


'Delete row 2 and the last row containing sum totals
    Rows(2).Delete


    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Worksheets(1).Rows(lastRow & ":" & lastRow).Delete Shift:=xlUp


'Delete column A and column H
    Columns(1).EntireColumn.Delete
    Columns(8).EntireColumn.Delete






''Parse column F to be Account Number and Description
'Insert 2 Columns to the left of Column G
    Columns("F:F").Select
    Columns("G:I").Insert Shift:=xlToRight, _
      CopyOrigin:=xlFormatFromLeftOrAbove












'THIS IS THE SECTION THAT IS GIVING ME PROBLEMS
'###############################################
'Test the name of the file to determine how to separate text into columns
If sNameOfWorkbook = "TBL_JPIIA" Then
    Range("F:F").Select
    Selection.TextToColumns Destination:=Range("F1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(4, 1), Array(7, 1)), TrailingMinusNumbers:= _
        True
    'Delete unwated columns
        Columns(7).EntireColumn.Delete
        Columns(8).EntireColumn.Delete
        Columns(12).EntireColumn.Delete
    
    
ElseIf sNameOfWorkbook = "TBL_MUSGA" Or sNameOfWorkbook = "TBL_FLBGA" Then
    Range("F:F").Select
    Selection.TextToColumns Destination:=Range("G1"), DataType:=xlFixedWidth, _
        OtherChar:=".", FieldInfo:=Array(Array(0, 1), Array(6, 1), Array(9, 1)), _
        TrailingMinusNumbers:=True
    'Delete unwated columns
        Columns(6).EntireColumn.Delete
        Columns(7).EntireColumn.Delete
        Columns(12).EntireColumn.Delete
    
End If      
'###############################################










'Rename Headers
    Range("F1").FormulaR1C1 = "Account"
    Range("G1").FormulaR1C1 = "Description"
    Range("L1").FormulaR1C1 = "Notes"


'Set column data types
    Range("A:A,C:I,L:L").Select
    Selection.NumberFormat = "@"
    
    Range("J:K").Select
    Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
    
    Columns("B:B").Select
    Selection.NumberFormat = "m/d/yyyy"
    
'Create a Table out of data
    ActiveSheet.UsedRange.Name = "TestRange"
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("TestRange"), , xlYes).Name = "LEDGER_DETAIL"
    
'Autofit all the columns
    Columns("A:L").EntireColumn.AutoFit
    
'Rename sheet to Workbook name
    ActiveSheet.Name = NameOfWorkbook


'Resume to start position
    Range("A1").Select
    
'Turn off Page Breaks, if Any
    ActiveSheet.DisplayPageBreaks = False


Else
End If


'Turn off Optimize_VBA
    Optimize_VBA False
    
'Turn off Page Breaks, if Any
    ActiveSheet.DisplayPageBreaks = False
    


Error_Handler_Exit:
    Exit Sub


Error_Handler:
    Select Case Err.Number
        Case 94
            Err.Clear
            Resume Error_Handler_Exit
        Case Else
            MsgBox "Error No. " & Err.Number & vbCrLf & "Description: " & Err.Description, vbExclamation, "Database Error"
            Err.Clear
            Resume Error_Handler_Exit
    End Select
End Sub