worksheet_change() Run-time Error '1004' The extract range has a missing or invalid field name.

[jakel27]

Hi,

I have worksheet A which has macros worksheet_change(), so macros run automatically if a cell changes. This works perfectly fine to copy and paste cells onto worksheet B (HTransWS) if I make any changes to Worksheet A.

But, if I use a macro to paste values from another WorkBook into Worksheet A, the worksheet macro doesn't work and gives me a run-time error '1004'. Can someone help me understand why this is?

i.e. Worksheet A macro works fine if I change something on the worksheet. But breaks if I extract data from another workbook and paste it onto Worksheet A.

Is there something I'm missing about AdvancedFilter?
----------------------------------------------
Sub Copy()

Workbooks("Extract.xlsm").Worksheets("SEC Sheet Delay Data").Range("B15567:R15567").Copy _
Workbooks("LV RTAS - Parked.xlsm").Worksheets("SEC Sheet Delay Data").Range("B15572")

End Sub
---------------------------------------------

Run-time Error '1004' The extract range has a missing or invalid field name.

 

[Alex Blakenburg]

Do you have something in R1 that is different to what is in A1 (Filter Field Column Heading) ?
It needs R1 (Output Field Column Heading) to be either blank or the same value as A1

 

[jakel27]

Do you have something in R1 that is different to what is in A1 (Filter Field Column Heading) ?
It needs R1 (Output Field Column Heading) to be either blank or the same value as A1

Thanks for the response Alex.

As mentioned, Worksheet A code should trigger if I change anything to a cell on Worksheet A, and it works fine this way, i.e. to paste data to Worksheet B. But, if I use a macro to paste data from another Workbook onto Worksheet A to trigger the event, then I get this error for some odd reason.

This is worksheet B. These are my Headers which show Column "A" and "R" are the same.

 

[Alex Blakenburg]

I am a bit confused.
You are copying from: Workbooks("Extract.xlsm").Worksheets("SEC Sheet Delay Data")
To: Workbooks("LV RTAS - Parked.xlsm").Worksheets("SEC Sheet Delay Data")

What is the name of the workbook that contains: HTransWS ?
What is the actual sheet name of HTrans ?
Show me the Set HTrans = line and if there is an associated line for setting the workbook show me that too ?
What sheet has the Worksheet_Change code behind it ?

 

[jakel27]

I am a bit confused.
You are copying from: Workbooks("Extract.xlsm").Worksheets("SEC Sheet Delay Data")
To: Workbooks("LV RTAS - Parked.xlsm").Worksheets("SEC Sheet Delay Data")

What is the name of the workbook that contains: HTransWS ?
What is the actual sheet name of HTrans ?
Show me the Set HTrans = line and if there is an associated line for setting the workbook show me that too ?
What sheet has the Worksheet_Change code behind it ?

Yeah it was more of a test of my worksheet_change() code on worksheet A, and it does pose problems I've had in the past.

Order goes like this...
1. Workbook Extract has Worksheet SEC Sheet Delay Data with macro to copy and paste data to Workbook LV RTAS - Parked
2. Workbook LV RTAS - Parked has Worksheet SEC Sheet Delay Data and this has the worksheet_change() macro, so when I do anything to the page, it should copy and paste data to Worksheet HTransWS
3. HTransWS worksheet name is called "Timestamp"

---------------------------------------------------------------------------------------------------------------------------
Dim TransIDField As Range
Dim TransIDCell As Range
Dim ATransWS As Worksheet
Dim HTransWS As Worksheet

Set ATransWS = Worksheets("SEC Sheet delay data")
Set TransIDField = ATransWS.Range("C15000", ATransWS.Range("C15000").End(xlDown))
Set HTransWS = Worksheets("Timestamp")

HTransWS.Range("A2:P" & Rows.Count).Clear
'HTransWS.Range("A2").EntireRow.Clear Less Laggy ALTERNATIVE to be fixed further

For Each TransIDCell In TransIDField

If TransIDCell.Interior.Color = RGB(255, 0, 0) Then

TransIDCell.Resize(1, 16).Copy Destination:= _
HTransWS.Range("A1").Offset(HTransWS.Rows.Count - 1, 0).End(xlUp).Offset(1, 0)

End If

Next TransIDCell

With HTransWS
.Range("A1", .Range("A1").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("R1"), Unique:=True 'This line creates issues from macro but works fine without macro.

Dim lngMyRow As Long

For lngMyRow = 2 To .Cells(Rows.Count, "S").End(xlUp).Row
If .Range("S" & lngMyRow) > 10 Then
.Range("U" & lngMyRow).Value = .Range("R" & lngMyRow).Value
ElseIf .Range("S" & lngMyRow) <= 10 Then
.Range("U" & lngMyRow).Clear
End If
Next lngMyRow

End With

 

[Alex Blakenburg]

I am in Australia and login off for the night.
In the meantime try adding the debug.print line I have below and see if what you get in the immediate window is the right workbook, worksheet, range address for the filter.

With HTransWS
    Debug.Print .Range("A1", .Range("A1").End(xlDown)).Address(, , , 1)
    .Range("A1", .Range("A1").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, copytorange:=.Range("L1"), Unique:=True

 

[jakel27]

I am in Australia and login off for the night.
In the meantime try adding the debug.print line I have below and see if what you get in the immediate window is the right workbook, worksheet, range address for the filter.

With HTransWS
    Debug.Print .Range("A1", .Range("A1").End(xlDown)).Address(, , , 1)
    .Range("A1", .Range("A1").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, copytorange:=.Range("L1"), Unique:=True

Hi, I am in Australia too, so I wasn't up when you posted this either. Thanks for the help.

I added the Debug code and the code went past is and stopped again at the .Range line.

I might try to use another code for finding unique values. I find it very strange this code works if I do anything on the worksheet. But if extracting data from the other workbook, it causes the 1004 error.

 

[Alex Blakenburg]

It sounds like you have not used the immediate window before.
The Debug.Print line was positioned before the problem line and should have printed the details of the problematic Range to the immediate window.
If you can't see it when you are in the VB editor hit Ctrl+G and it should appear.
What do you see in the immediate window when you run the code ?

 

[jakel27]

Sorry I'm very new to VBA.
The LV RTAS- Parked lines show up when the code works (ie. when I manually make a change to the worksheet and it copies unique values to the "Timestamp" worksheet.

The [Extract] line is the line that shows when I use the Extract workbook to paste values into my worksheet to prompt the change.

The extract code does successfully pull data from extract workbook to LV RTAS - parked workbook. Just when copying data from LV RTAS - Parked "SEC..." Worksheet to "Timestamp" worksheet, I get this error, if this helps.

Thanks again for all your time. It's okay if you can't solve it. My code is terrible and my way of explaining is even worse.

 

[Alex Blakenburg]

I don't have all the pieces of you how workbooks and code are hanging together.
eg which of the 2 workbooks contains the code that you are showing us and that it is failing on.

My concern is that these lines below, don't reference the workbook and as such will use the ActiveWorkbook.

Set ATransWS = Worksheets("SEC Sheet delay data")
Set TransIDField = ATransWS.Range("C15000", ATransWS.Range("C15000").End(xlDown))
Set HTransWS = Worksheets("Timestamp")

Your immediate window and comments seem to support my thoughts that when you do the paste from the Extract workbook, the Extract workbook is the ActiveWorkbook and hence the set commands are all referring to the wrong workbook. The fact the set command is not falling over indicates that the Extract workbook also contains those sheet names (hence my confusion)

If the code is in the workbook LV RTAS - Parked, then try the below

    With ThisWorkbook
        Set ATransWS = .Worksheets("SEC Sheet delay data")
        Set TransIDField = ATransWS.Range("C15000", ATransWS.Range("C15000").End(xlDown))
        Set HTransWS = .Worksheets("Timestamp")
    End With