Error 1004 when attempting to copy from delimited file

[gitmeto]

I have generated a code using the Macro recorder that works fine if it sits on its own. However, when I attempt to implement an event, it will not select the correct range. It seems as though that it is not seeing the range that I am attempting to copy data from. Any assistance would be greatly appreciated. It is erroring out on the line "Range("B3:B4").Select.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lRow As Long
    Dim KeyCells As Range
    Dim strPath, strFile As String

    lRow = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row
   
    'Assign variables for path and file
    strPath = Range("C" & lRow).Value
    strFile = Range("D" & lRow).Value
   
   
    ' The variable KeyCells contains the cells that will cause d' to be calculated when they are changed.
    Set KeyCells = Range("D" & lRow)

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
        Workbooks.OpenText Filename:= _
            strPath, Origin:= _
            437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
            ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False _
            , Space:=False, Other:=True, OtherChar:=":", FieldInfo:=Array(Array(1, 1 _
            ), Array(2, 1)), TrailingMinusNumbers:=True
        Range("B3:B4").Select
        Selection.Copy
        Windows("test.xlsm").Activate
        Range("E" & lRow).Select
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=True
        Windows(strFile).Activate
        ActiveWindow.Close
   
    MsgBox "Hello World"

    End If

End Sub

 

[JoeMo]

Does the range you want to select belong to the text file or to the worksheet that contains the change event code? You cannot select a range that's not on the activesheet, and you may need to qualify the sheet and perhaps even the workbook. Also, you don't need to select the sheet to copy a range on it.

 

[gitmeto]

Does the range you want to select belong to the text file or to the worksheet that contains the change event code? You cannot select a range that's not on the activesheet, and you may need to qualify the sheet and perhaps even the workbook. Also, you don't need to select the sheet to copy a range on it.T

Thanks for responding so quickly @JoeMo. The delimited file is opened up in excel and is activated, but the first cell "A1" is selected when I want to select "B3:B4". However it is stating that it is out of range. So this seems to tell us that it is opening the text file as a delimited file properly, but not seeing the second column. Again, if I run this without the event handler, it works fine.

 

[JoeMo]

Have you tried putting a breakpoint in at the offending line (Range("B3:B4").Select), looking at the newly opened txt file when using the event handler and comparing it to the txt file that opens from just running the module w/o using an event handler to see if there's a difference or differences that might provide a clue?

 

[gitmeto]

Have you tried putting a breakpoint in at the offending line (Range("B3:B4").Select), looking at the newly opened txt file when using the event handler and comparing it to the txt file that opens from just running the module w/o using an event handler to see if there's a difference or differences that might provide a clue?

@JoeMo, I have been able to get things working. I have a worksheet that has formulas that are calculated when cells are populated in previous columns on the same row. What I saw was that the macros would run first without the calculations being performed which caused it to error out because the appropriate values were not calculated in their respective cells.

To resolve the issue, I changed the attribute of the sheet to calculate automatically and this seem to fix the problem. However, prior to that I cleaned up the event handler by moving specific subs to their own modules and calling them if the event occurred (cell change), so I might have fixed it in conjunction with changing the attribute. But changing the attribute was necessary.

Now I just need to get the event handler to recognize the a table name and column header so that I can make this fully dynamic. But I will open up a separate thread as this is a different issue.

Below you will see the working code.

Thanks for all your help Joe. Happy Thanksgiving!

Private Sub Worksheet_Change(ByVal Target As Range)

    'RunTime Reduction
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationAutomatic
    End With
    
    Dim lRow As Long
    
    lRow = Cells.Find(What:="*", _
                        After:=Range("A1"), _
                        LookAt:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Row

    'Watch to see if a value in Fiile_Path column changes
    If Not Application.Intersect(Target, Range(Cells(5, 1), Cells(lRow, 9))) Is Nothing Then
        MsgBox "Change Detected"
        Call copy_results_text
        Call multiple_entries_check
        Call clean_up
    End If
    
    'Reset Application Features
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    
End Sub

 

[JoeMo]

Glad you got it working - thanks for the reply.