Splitting a Workbook name and filling Two columns with the two pcs of data.

[Bill Williamson]

I have tride a few things but Im doing something wrong. I am trying to split a Workbook name such as "Aurora E341934012.xlsm"
I have my code inserting the new columns. Now I need the Customer "Aurora" in ths case name to fill column B to the last used row in column C and the
Cso# "E341934012" to fill column A to the last used row in column C". The Customer name can vary in length so can the CSO#. But the name will always be first and have a Space after it, if Better it could have an "_".


I did not include the codes I have tried, since none worked.

Thankyou for any help.

Bill Williamson

 

[Bill Williamson]

I Have decided to include the code I have thus far. The Sub is working well, just cant seem to get the Function result to go into cell A1 let alone fill the column.

Sub Sitelinedatamacro()
'
' Siteline Data Macro
' Shorten Data File from Siteline
'
' Keyboard Shortcut: Ctrl+g
'
    Columns("A:B").Select
    Selection.ClearContents
    Selection.Delete Shift:=xlToLeft
    Columns("B:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("C:AA").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:S").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:V").Select
    Selection.Delete Shift:=xlToLeft
    ActiveWindow.SmallScroll ToRight:=-3
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("E4").Select
    Columns("B").Select
    Selection.Cut
    Columns("D").Select
    Selection.Insert Shift:=xlToRight
    
'Removes "J" from Job Numbers
    Columns("A").Select
    Selection.Replace "j", ""
    Columns("A").Select
    Selection.Replace "ob", "Job#"
 'Renames Part Number
    Columns("C").Select
    Selection.Replace "item", "Part#"
 'Renames Quantity
    Columns("D").Select
    Selection.Replace "Received", "Quantity"
    
    
'Centers and Aligns Data to Correct Format
     Cells.Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
   
   
 'Delete any Row that Part Number ends in "C"
  
   
   'Sub delX()
    Dim lr As Long, i As Long
    lr = Range("C" & Rows.Count).End(xlUp).Row
    For i = lr To 1 Step -1
        If InStr(Range("C" & i), "C") > 0 Then
            Range("C" & i).EntireRow.Delete
        End If
    Next i
    ActiveSheet.Columns("A:B").Insert Shift:=xlToRight
    ActiveSheet.Name = "Eport"
   
   ' ActiveWorkbook.Close savechanges:=True
   

End Sub
Function CSO() As String
Dim lngstart As Long
Dim lngEnd As Long
    'Find CSO# Starting and end Positions
    ' Which will be between the " " and the "."
    lngstart = InStr(ThisWorkbook.Name, " ")
    lngEnd = InStr(ThisWorkbook.Name, ".")
    
    'Pull CSO# out of file name
    
    CSO = Mid(ThisWorkbook.Name, lngstart + 1, lngEnd - lngstart - 1)
    a1.Value = CSO
    
End Function


[Code/]


Any Ideas?

Thanks,

Bill Williamson

 

[Bill Williamson]

I was able to figure out how to extract the two pieces of information myself, probably not the correct way, but works.