adding a new column for file name when importing

[22newbie]

Hi all,
I have a working code where when I am importing sheet from multiple workbooks, it will add a column to include the first 6 characters of the filename. This code works perfectly fine when there are data in all the columns/rows

'Adding last column for the first 6 characters of the file name
            ThisWorkbook.Sheets("Master").Activate
            desWS.Cells(desWS.Rows.Count, "A").End(xlUp).End(xlToRight).Offset(0, 1).End(xlUp).Offset(1, 0).Select
            ActiveCell.FormulaR1C1 = Left(strExtension, 6)
            Selection.Copy
            Selection.Offset(0, -1).Select
            Selection.End(xlDown).Select
            Selection.Offset(0, 1).Select
            Range(Selection, Selection.End(xlUp)).Select
            ActiveSheet.Paste

Now the problem I am facing importing is the sheet has at least 20 columns. Some columns are blank with no data like column E and F. When I run the code, it recognizes E as the end of the row and adds a new column with the filename. How do I fix that? The new column should be at the end of the 20 column.

Hope I made sense.

 

[Alex Blakenburg]

The equivalent of xlup on rows is xltoleft for columns. They rely on identifying a column that is populated to the last row and a row that is populated to the last column.
Typically this would be Column A and Row 1 respectively.

If you are uncertain as to which column or row will always be the longest then it is safer to use find.

Sub FillFileName()

    Dim desWB As Workbook
    Dim desWS As Worksheet
    Dim lastRow As Long, lastColumn As Long
    
    Set desWB = ThisWorkbook
    Set desWS = desWB.Worksheets("Master")
    
    Dim strExtension As String
    strExtension = "Filename"
    
    With desWS
        lastRow = .Cells.Find(What:="*" _
                    , Lookat:=xlPart _
                    , LookIn:=xlFormulas _
                    , searchorder:=xlByRows _
                    , searchdirection:=xlPrevious).Row
    
        lastColumn = .Cells.Find(What:="*" _
                    , Lookat:=xlPart _
                    , LookIn:=xlFormulas _
                    , searchorder:=xlByColumns _
                    , searchdirection:=xlPrevious).Column
                    
        .Range(.Cells(1, lastColumn + 1), .Cells(lastRow, lastColumn + 1)).Value = Left(strExtension, 6)
        
    End With
    
End Sub