VBA Runtime Error

[Johnny4strings]

Hello Everyone,

I am having a slight issue with some code I am trying to run.
Some backstory: This VBA is from a spreadsheet that creates some tabs when executing the macro/clicking on macro button with the ultimate goal of pulling text from a .txt that is updated daily. This code is part of a larger set of data that pulls more tabs/data-I just wanted to post the information that was triggering the debugger.

As of now I am getting 1004 error message upon execute of macro: Application-defined or object defined error. Error where debugger has identified the problem is bolded. Code begins below dotted lines. As always, thank you gurus in advance

…………………………………………………………………

'FM1 Data add
On Error Resume Next
Sheets("FM1 " & Format(Now, "mm.dd.yy")).Visible = True
Sheets("FM1 " & Format(Now, "m.dd.yy")).Visible = True
Sheets("FM1 " & Format(Now - 1, "mm.dd.yy")).Visible = True
Sheets("FM1 " & Format(Now - 1, "m.dd.yy")).Visible = True
Sheets("FM1 " & Format(Now - 2, "mm.dd.yy")).Visible = True
Sheets("FM1 " & Format(Now - 2, "m.dd.yy")).Visible = True
Sheets("FM1 " & Format(Now - 3, "mm.dd.yy")).Visible = True
Sheets("FM1 " & Format(Now - 3, "m.dd.yy")).Visible = True
Sheets("FM1 " & Format(Now - 4, "mm.dd.yy")).Visible = True
Sheets("FM1 " & Format(Now - 4, "m.dd.yy")).Visible = True
On Error GoTo 0
 
On Error Resume Next
Sheets("FM1 " & Format(Now, "mm.dd.yy")).Select
Sheets("FM1 " & Format(Now, "m.dd.yy")).Select
Sheets("FM1 " & Format(Now - 1, "mm.dd.yy")).Select
Sheets("FM1 " & Format(Now - 1, "m.dd.yy")).Select
Sheets("FM1 " & Format(Now - 2, "mm.dd.yy")).Select
Sheets("FM1 " & Format(Now - 2, "m.dd.yy")).Select
Sheets("FM1 " & Format(Now - 3, "mm.dd.yy")).Select
Sheets("FM1 " & Format(Now - 3, "m.dd.yy")).Select
Sheets("FM1 " & Format(Now - 4, "mm.dd.yy")).Select
Sheets("FM1 " & Format(Now - 4, "m.dd.yy")).Select
On Error GoTo 0
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete
Range("A2").Select
 
 
    ActiveWorkbook.Worksheets.Add
    ActiveSheet.Name = "FM1 Data Retrieval"
    On Error GoTo Errorhandler
    'File Path: FM1 (Within Quotations)
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;File:///\\drivepath\FM1.txt", _
        Destination:=Range("$A$1"))
               .Name = "FM1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    On Error GoTo 0
    Cells.Find(What:="Fund Code", After:=ActiveCell, LookIn:= _
    xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    FundCount = Selection.Count
    ActiveCell.Select
    For X = 1 To FundCount
    ActiveCell.Value = Trim(Selection.Value)
    ActiveCell.Offset(1, 0).Select
 
Next X
 
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
On Error Resume Next
Sheets("FM1 " & Format(Now, "mm.dd.yy")).Select
Sheets("FM1 " & Format(Now, "m.dd.yy")).Select
Sheets("FM1 " & Format(Now - 1, "mm.dd.yy")).Select
Sheets("FM1 " & Format(Now - 1, "m.dd.yy")).Select
Sheets("FM1 " & Format(Now - 2, "mm.dd.yy")).Select
Sheets("FM1 " & Format(Now - 2, "m.dd.yy")).Select
Sheets("FM1 " & Format(Now - 3, "mm.dd.yy")).Select
Sheets("FM1 " & Format(Now - 3, "m.dd.yy")).Select
Sheets("FM1 " & Format(Now - 4, "mm.dd.yy")).Select
Sheets("FM1 " & Format(Now - 4, "m.dd.yy")).Select
On Error GoTo 0
ActiveSheet.Paste
Application.DisplayAlerts = False
Sheets("FM1 Data Retrieval").Delete
ActiveSheet.Name = "FM1 " & Format(Now, "mm.dd.yy")
Application.DisplayAlerts = True
Range("A2").Select
Selection.End(xlToRight).Select
[B]ActiveCell.Offset(0, 1).Select[/B]
ActiveCell.Value = "=LEFT(TRIM($J2),2)"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "=TRIM(LEFT($I2,8))"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "=IF(LEFT($S2,4)=" & """RC17""" & "," & """Y""" & "," _
& """N""" & ")"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "=RIGHT($S2,2)"
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
 Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown

 

[Fluff]

At a guess I'd suggest that all the cells to the right of A2 are empty.

 

[Johnny4strings]

Thank you for the help! I will check the FM1 text file to see if the right of A2 is empty.

 

[Fluff]

The other possibility is that all columns in that row have a value of some sort.

 

[Johnny4strings]

Here is the first few lines of the .txt file (wondering if the omission of space after the comma in the first line after "division" is throwing this macro off):


Division,As of, Fiscal, Fund, Org, Locale, Descrip, Prog, Proj, Obj, Class, Annual, YTD, Commit, Oblig, Spent, BF
88, 31-DEC-2018, 2019, KK, 11-55, SAC, LAWYER, 8800.9999, TURK099-000, KO129999, 0, 0, 0, 0, 0, 1111.8, -1111.8
69, 31-DEC-2018, 2019, MM, 22-222, MUF, CIO, 9999.00000, NOPE099-ARF, MUFB9999, 0, 7000000, 7000000, 210999, 99994.37, 0, 556631.63
11, 31-DEC-2018, 2019, AB, 02-10, MAD, CDO, 8888.00012, MADE012-222, KO000000, 0, 9999991.2, 9999991.2, 0, 0, 0, 9999991.2

 

[Fluff]

Absolutely no idea.
Run the code & when it fails where is the active cell?

 

[Johnny4strings]

Hi and thanks for replying. It looks like it fails at cell XFD2 on the same page as the macro execution button, no matter what cell I click after the macro runs.

 

[Fluff]

In that case it's as I suspected, either all cells in that row from Col B onwards are empty, or have a value.

 

[Johnny4strings]

Thank you! Any way I can correct the VBA to accommodate for this? This project was handed down to me and I am just trying to make it run. I figure some modification on the code would work-do you have any thoughts on this?
I thank you in advance.

 

[Fluff]

As I haven't got a clue what your code is doing, I've no idea how to "correct it".
Are you able to extract the data successfully, if so when you paste it to the other sheet does everything get copied successfully?