the extract range has missing or illegal field name

[cscrajasekhar]

[TABLE="width: 1040"]
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody></tbody>[/TABLE]

When I add a new row at the end and insert items in godown column and I want to advance filter xlfiltercopy to another range for unique values in godown column I am getting error “the extract range has missing or illegal field name”. I am using worksheet change event. Please help.
[TABLE="width: 668"]
<tbody>[TR]
[TD]Bill No.
[/TD]
[TD]Bill Date
[/TD]
[TD]Ack No.
[/TD]
[TD]Date
[/TD]
[TD]Acceptance No.
[/TD]
[TD]Acceptance Date
[/TD]
[TD]GODOWN
[/TD]
[/TR]
[TR]
[TD]16403
[/TD]
[TD]24-10-2019
[/TD]
[TD]4470
[/TD]
[TD]30-09-2019
[/TD]
[TD]4470
[/TD]
[TD]30-09-2019
[/TD]
[TD]BSC GUDIVADA
[/TD]
[/TR]
[TR]
[TD]16404
[/TD]
[TD]24-10-2019
[/TD]
[TD]4496
[/TD]
[TD]16-10-2019
[/TD]
[TD]4496
[/TD]
[TD]16-10-2019
[/TD]
[TD]BSC GUDIVADA
[/TD]
[/TR]
[TR]
[TD]16405
[/TD]
[TD]24-10-2019
[/TD]
[TD]3923
[/TD]
[TD]07-10-2019
[/TD]
[TD]2248
[/TD]
[TD]07-10-2019
[/TD]
[TD]BSC HANUMANJUNCTION
[/TD]
[/TR]
[TR]
[TD]16406
[/TD]
[TD]24-10-2019
[/TD]
[TD]3919
[/TD]
[TD]03-10-2019
[/TD]
[TD]2245
[/TD]
[TD]03-10-2019
[/TD]
[TD]BSC HANUMANJUNCTION
[/TD]
[/TR]
[TR]
[TD]16407
[/TD]
[TD]24-10-2019
[/TD]
[TD]3915
[/TD]
[TD]30-09-2019
[/TD]
[TD]4346
[/TD]
[TD]30-09-2019
[/TD]
[TD]BSC HANUMANJUNCTION
[/TD]
[/TR]
[TR]
[TD]16408
[/TD]
[TD]24-10-2019
[/TD]
[TD]3920
[/TD]
[TD]03-10-2019
[/TD]
[TD]2246
[/TD]
[TD]03-10-2019
[/TD]
[TD]BSC HANUMANJUNCTION
[/TD]
[/TR]
[TR]
[TD]16409
[/TD]
[TD]24-10-2019
[/TD]
[TD]3233
[/TD]
[TD]19-06-2019
[/TD]
[TD]296
[/TD]
[TD]19-06-2019
[/TD]
[TD]BSC HANUMANJUNCTION
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[cscrajasekhar]

Private Sub Worksheet_Change(ByVal Target As Range)  If Not Intersect(Target, Range("J6:J1048576")) Is Nothing Then
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    lw = Cells(Rows.Count, "B").End(xlUp).Row
    Application.EnableEvents = False
  
[COLOR=#0000ff][U][I][B]    ActiveSheet.Range("J6:J" & lastrow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ActiveSheet.Range("B" & lastrow + 5), Unique:=True

[/B][/I][/U][/COLOR]
    Application.EnableEvents = True
  End If
End Sub

 

[Peter_SSs]

But I am afraid i am not clear explaining the code or problem.

Unfortunately that means I have to try to guess some of it.

One more try. If this does not work you may have to upload a dummy file (with any sensitive data removed or disguised) to a file-share site and provide a link here so we can look at the actual file/problem.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim lastrow As Long, lw As Long
  
  If Not Intersect(Target, Range("J6:J1048576")) Is Nothing Then
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    lw = Cells(Rows.Count, "B").End(xlUp).Row
    Application.EnableEvents = False
    If lw > lastrow Then Rows(lastrow + 1 & ":" & lw).Delete
    ActiveSheet.Range("J6:J" & lastrow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ActiveSheet.Range("B" & lastrow + 5), Unique:=True
    Application.EnableEvents = True
  End If
End Sub

 

[cscrajasekhar]

That worked well. Thanks. I have added some more lines to suit my needs. Now there is one more problem. If there are blank cells in the extract range, those blank cells are to be skiped. Can that be done in the present code as suggested by you. Also, can the extracted range be arranged in alphabetical order?

 

[Peter_SSs]

Try adding this line where shown

ActiveSheet.Range("J6:J" & lastrow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ActiveSheet.Range("B" & lastrow + 5), Unique:=True
Range("B" & lastrow + 5, Range("B" & Rows.Count).End(xlUp)).Sort Key1:=Range("B" & lastrow + 5), Order1:=xlAscending, Header:=xlYes
Application.EnableEvents = True

 

[cscrajasekhar]

That is a direct hit. Many many thanks for the support and advice given. If time is available to you, I shall seek some advice from you. I am not expert in these vba codes. But I developed some to suit my needs only. If you can spare some time and energy, I shall give my code and sample file to you. I am glad to receive improvements to the code. But only if you have sufficient time. If willing, suggest me some mail id to send the file.

 

[Peter_SSs]

That is a direct hit. Many many thanks for the support and advice given.

You're welcome.

If time is available to you, I shall seek some advice from you. I am not expert in these vba codes. But I developed some to suit my needs only. If you can spare some time and energy, I shall give my code and sample file to you. I am glad to receive improvements to the code. But only if you have sufficient time. If willing, suggest me some mail id to send the file.

Sorry, the forum is for public help, not private - refer to #4 of the Forum Rules.

If you have any specific questions about your code you would need to post them in the forum as normal.

 

[cscrajasekhar]

That's good and nice. Only posting through the forum will be done by me.