So I have some code (see below) that works as intended when I was running it within the desired workbook.
However, when I added it to my Personal Workbook (more accurately an Add In i built) it does not execute they way I want it to, and here is why.
There is an IF Else If Statement which looks at the name of the workbook, and based on that, executes a set of commands. The problem now is (I believe) that the code is not seeing the name of the workbook, but rather the name of my Add In.
My question is this, how do I pull the name of the workbook, and not the name of my Add in?
The name of my Add in is "LAST_NAME.xlam
See code snipit:
Code
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
Display More
Any help on this particular issue or any suggestions on improving this code is greatly appreciated