Macro works but generates an error

[Jonlowery]

I have a macro that I made that runs a query that is saved on the computer, filters it to only show the previous months data, then saves it to a specified location with a name based off of the date that you run the macro. Unfortunately, while the macro works, it generates an error and I have had no luck in getting rid of the error. It is a generic error that says "Unexpected Error - Something went wrong. If the problem continues, please restart Excel." I have tried Application.DisplayAlerts = False as well as On Error Resume Next and neither one stops the errors from generating. Below is the code, any advice you can give would be greatly appreciated. Thanks!

Workbooks.OpenDatabase Filename:= _
        "T:\Department\Investment Operations\_Safekeeping\Queries\CLR STK monthly query for analysis.dqy" _
        , CommandText:=Array( _
        "SELECT customer_0.""cust-num"", customer_0.dda, customer_0.""cust-na1"", customer_0.city, customer_0.state, trade_0.""se" _
        , _
        "c-id"", trade_0.ticket, trade_0.""port-id"", trade_0.face, trade_0.par, trade_0.""tot-amt"", trade_0.""settle-dt"", trade_" _
        , _
        "0.psc, trade_0.""del-cd"", trade_0.""del-inst-1"", trade_0.""del-inst-2"", trade_0.""misc-inst""  FROM TRADING1.PUB.custome" _
        , _
        "r customer_0, TRADING1.PUB.trade trade_0  WHERE customer_0.""cust-num"" = trade_0.""cust-num"" AND ((trade_0.""settle-dt" _
        , """>={d '2018-05-01'}) AND (trade_0.""port-id""='clr stk'))"), CommandType:= _
        xlCmdSql, ImportDataAs:=xlTable
   

Dim lo As ListObject
Dim iCol As Long

  'Set reference to the first Table on the sheet
  Set lo = Sheets("CLR STK monthly query for analy").ListObjects(1)
  
  'Set filter field
  iCol = lo.ListColumns("settle-dt").Index
   
  'Clear Filters
  lo.AutoFilter.ShowAllData
  
  'Operator:=xlFilterDynamic
  'Criteria1:= one of the following enumerations
  
  ' Value Constant
  ' 1     xlFilterToday
  ' 2     xlFilterYesterday
  ' 3     xlFilterTomorrow
  ' 4     xlFilterThisWeek
  ' 5     xlFilterLastWeek
  ' 6     xlFilterNextWeek
  ' 7     xlFilterThisMonth
  ' 8     xlFilterLastMonth
  ' 9     xlFilterNextMonth
  ' 10    xlFilterThisQuarter
  ' 11    xlFilterLastQuarter
  ' 12    xlFilterNextQuarter
  ' 13    xlFilterThisYear
  ' 14    xlFilterLastYear
  ' 15    xlFilterNextYear
  ' 16    xlFilterYearToDate
  ' 17    xlFilterAllDatesInPeriodQuarter1
  ' 18    xlFilterAllDatesInPeriodQuarter2
  ' 19    xlFilterAllDatesInPeriodQuarter3
  ' 20    xlFilterAllDatesInPeriodQuarter4
  ' 21    xlFilterAllDatesInPeriodJanuary
  ' 22    xlFilterAllDatesInPeriodFebruray <-February is misspelled
  ' 23    xlFilterAllDatesInPeriodMarch
  ' 24    xlFilterAllDatesInPeriodApril
  ' 25    xlFilterAllDatesInPeriodMay
  ' 26    xlFilterAllDatesInPeriodJune
  ' 27    xlFilterAllDatesInPeriodJuly
  ' 28    xlFilterAllDatesInPeriodAugust
  ' 29    xlFilterAllDatesInPeriodSeptember
  ' 30    xlFilterAllDatesInPeriodOctober
  ' 31    xlFilterAllDatesInPeriodNovember
  ' 32    xlFilterAllDatesInPeriodDecember
      
  With lo.Range
    
    'All dates in January (across all years)
    .AutoFilter Field:=iCol, _
                Operator:=xlFilterDynamic, _
                Criteria1:=xlFilterLastMonth

  
  End With


    Range("Table_CLR_STK_monthly_query_for_analysis[[#Headers],[cust-num]]").Select
    Selection.Copy
    Range("Table_CLR_STK_monthly_query_for_analysis[[#Headers],[cust-num]]").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Name = "DTC SETTLEMENTS TOTAL"
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("C:P").Select
    Selection.Delete Shift:=xlToLeft
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=RIGHT(RC[-2],7)"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C265")
    Range("C2:C265").Select
    Columns("C:C").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-2],RC[-2])"
    Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D265")
    Range("D2:D265").Select
    Columns("D:D").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Customer Name"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "DDA"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Volume"
    Range("B1").Select
    Columns("A:A").EntireColumn.AutoFit
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Columns("A:C").Select
    ActiveSheet.Range("$A$1:$C$265").RemoveDuplicates Columns:=Array(1, 2, 3), _
        Header:=xlYes
Cells.Replace What:="0", Replacement:="", LookAt:=xlWhole, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False


x = Weekday(Date, vbSunday)
Select Case x
    Case 1
        x = 2
    Case 2
        x = 3
    Case Else
    x = 1
End Select


ActiveWorkbook.SaveAs Filename:="T:\Department\Investment Operations\_Safekeeping\EOM Reporting\2019\Manual Additional Post to DTC Settlements " & _
    Format(Date - x, "mm-yyyy"), FileFormat:=51, Password:="", _
    WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

ActiveWorkbook.Close

 

[mole999]

using f8 can you step through and identify where the failure occurs

 

[Jonlowery]

I have several macros like this and they all seem to get hung up on opening the DQY file. I have tried remaking the queries within the macro to no success. The macro still opens the dqy file but the error still appears.