Error 1004 when attempting to copy from delimited file

gitmeto

Board Regular
Joined
Nov 24, 2021
Messages
60
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.

Rich (BB code):
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
 
Last edited by a moderator:

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,913
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Board Regular
Joined
Nov 24, 2021
Messages
60
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,913
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Board Regular
Joined
Nov 24, 2021
Messages
60
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!

VBA Code:
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,913
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Glad you got it working - thanks for the reply.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,329
Messages
5,836,675
Members
430,444
Latest member
WrenchBoy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top