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.

 

[jakel27]

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

Has anyone told you you're a genius. Thank you so much for your support!

You didn't have to help but you did. I don't know how you picked up on that. Thanks a billion!

 

[jakel27]

I'm ac

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

I'm actually so happy my whole code is working now. Been trying to build this for last 4 days of 10 hour shifts (new to VBA) and that was my last hurdle.

Thanks again, you're a legend!

 

[Alex Blakenburg]

It was my pleasure. Glad I could help.