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

 

[Johnny4strings]

...Its funny but the other forms I am pulling data from are not throwing 1004 errors, just this one for some reason. Seems like the macro passes the first form fine then gets hung up on the second form (the form I sent the VBA from). I have a third form to pass date from however I am uncertain if that "data pull" is functional or not at this point.

 

[Johnny4strings]

Hi it does appear that the data is being transferred from the remote drive and that the spreadsheet is creating the individual tabs and populating them with data. I am not required to paste anything as tabs are created with the macro; its just that a run time error keeps revealing itself and possibly causing issues.

 

[Fluff]

Shortly before you get the error, your code is pasting data to the active sheet. Is the data getting pasted & is it all the data?

 

[Johnny4strings]

Hi and thank you for replying. I do believe that this is in fact pasting all information to the worksheet. I worked with this last night and I think I have found the problem with the macro dropping me off at cell XDF2; the macro seems to be working fine now and as far as I can tell, the data is accurate. Fluff, than you SO much for your help and more importantly, time.
-John

 

[Fluff]

Glad you got it sorted & thanks for the feedback