0

I am currently making a report based off of Stats in my company. The raw data that I receive has date with a Time Stamp (2 Date Columns - Created/Received). To Remove the time stamp, I set the a custom format to m/d/yyyy and then Delimit each column separately which gives me the correct date. Once I assign my Macro to a button and add new Raw Data I receive this error ----

enter image description here

Does anyone have any tips or understand why I am getting this error even though I am only Delimiting one column at a time? 

Sub ComplianceOTRSMacro()
    ' ComplianceOTRSMacro Macro
    ' Format Date - Refresh Pivot Tables

    Columns("D:E").Select
    Selection.NumberFormat = "m/d/yyyy"
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("OTRSData[[#Headers],[Created]]") _
        , DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True

    Columns("E:E").Select
    Selection.TextToColumns Destination:=Range( _
        "OTRSData[[#Headers],[Close Time]]"), DataType:=xlDelimited, TextQualifier _
        :=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:= _
        False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1) _
        , TrailingMinusNumbers:=True
    Range("OTRSData[[#Headers],[Ticket'#]]").Select
    Sheets("Dashboard").Select
    ActiveWorkbook.RefreshAll
End Sub
PeterT
  • 8,232
  • 1
  • 17
  • 38
Littlecurt
  • 1
  • 1
  • Hey BigBen, Just edited my Question. Sorry this is my first post. – Littlecurt Jun 26 '19 at 14:31
  • It looks like you have a table with multiple columns, named `OTRSData`. In the `TextToColumns` function, it's unclear to the function what you're specifying as the column to convert because you're including the `[#Headers]` tag. Try reducing the reference to `OTRSData[Close Time]` and see if that works. – PeterT Jun 26 '19 at 14:38
  • You may also want to try [avoiding the use of selections in your code](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – cybernetic.nomad Jun 26 '19 at 15:27

0 Answers0