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]

 

[Peter_SSs]

I am using worksheet change event.

Since it is the code that is throwing the error, it might be beneficial if we could see that code.

 

[cscrajasekhar]

If Not Intersect(Target, Me.Range("J6:J1048576")) Is Nothing Then lastrow = Cells(Rows.Count, "A").End(xlUp).Row
lw = Cells(Rows.Count, "B").End(xlUp).Row
    
ActiveSheet.Range("J6:J" & lastrow).AdvancedFilter Action:=xlFilterCopy,  CopyToRange:=ActiveSheet.Range("B" & lastrow + 5), UNIQUE:=True

 

[Peter_SSs]

Thanks for the code. It does raise some more questions though. The code refers to column J and column A. This covers 10 columns. However the data you posted only contains 7 columns and we don't know which 7 columns that data is in.
So, we really need to know more about just what you have in columns A:J

Also, what are you trying to do with the variable lw? You find a value for it in the code but then never use that value anywhere.

 

[cscrajasekhar]

column A to column J contains the data.   A6 to J6 contains the headers.  lw is for determining the lastrow in B column after i paste the filtered values from j column.   i insert new row at the end of the data.  After fill the data for the row from column A to column J.  J column contains the data to be extracted from i.e, unique values.  after that i copy that unique values in column B after some 5 rows of the data.  and after pasting the unique values as said, in column B, i have to enter " total " at the end of the column B.  for that purpose i used the variable "lw".   i think i am clear. i have deleted some column data so as to fit sample data into the forum.  that is no problem i assume.

 

[cscrajasekhar]

[TABLE="width: 1040"]
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Bill No.[/TD]
[TD]Bill Date[/TD]
[TD]Mill Code[/TD]
[TD]Name of the Mill[/TD]
[TD]From[/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]SB14-1179[/TD]
[TD]LAKSHMI GANAPATHI RICE INDUSTRIES[/TD]
[TD]PEDAPALAPARRU[/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]SB14-1179[/TD]
[TD]LAKSHMI GANAPATHI RICE INDUSTRIES[/TD]
[TD]PEDAPALAPARRU[/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]SB14-661[/TD]
[TD]MANIKANTA TRADERS[/TD]
[TD]AGIRIPALLI[/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]SB14-661[/TD]
[TD]MANIKANTA TRADERS[/TD]
[TD]AGIRIPALLI[/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]SB14-661[/TD]
[TD]MANIKANTA TRADERS[/TD]
[TD]AGIRIPALLI[/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]SB14-283[/TD]
[TD]SRI VENKATESWARA RICE MILL [/TD]
[TD]MEERJAPURAM[/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]SB14-1741[/TD]
[TD]SRI RAMA KRISHNA RICE & FLOUR MILL [/TD]
[TD]RANGANNAGUDEM[/TD]
[TD]3233[/TD]
[TD]19-06-2019[/TD]
[TD]296[/TD]
[TD]19-06-2019[/TD]
[TD]BSC HANUMANJUNCTION[/TD]
[/TR]
</tbody>[/TABLE]

 

[cscrajasekhar]

here i posted the sample data gain with columns A through J.  thanks in advance.

 

[cscrajasekhar]

as soon as i enter data into the row i.e, j column, the data is to be extracted with unique values from column j and paste after say some 5 rows in column B after the lastrow in the main data.  i think i am clear.

 

[Peter_SSs]

Firstly, when replying with normal comments, please don't enclose them in Code tags as it make it quite hard to follow.

I'm afraid it isn't entirely clear but let's see if we can't work it out for you.

You say that lw is to determine the last row in column B. I agree that is true. However, you never actually use that value anywhere in your code. Everywhere else uses lastrow.
I have taken a guess at what you want to do with the lw variable in my suggested code below.

When you use a Worksheet_Change code to actually make a change on your worksheet (as yours does by making the unique list in column B) you should disable events, otherwise, when you make a change the code writes the list to the sheet which triggers the worksheet change code again when it is not needed.

See if this is any use. If it is not what you want you will have to try to clarify further. I have not been able to reproduce the error that you originally reported.

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
    ActiveSheet.Range("J6:J" & lastrow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ActiveSheet.Range("B" & lw + 5), Unique:=True
    Application.EnableEvents = True
  End If
End Sub

 

[cscrajasekhar]

Firstly, when replying with normal comments, please don't enclose them in Code tags as it make it quite hard to follow.

I'm afraid it isn't entirely clear but let's see if we can't work it out for you.

You say that lw is to determine the last row in column B. I agree that is true. However, you never actually use that value anywhere in your code. Everywhere else uses lastrow.
I have taken a guess at what you want to do with the lw variable in my suggested code below.

When you use a Worksheet_Change code to actually make a change on your worksheet (as yours does by making the unique list in column B) you should disable events, otherwise, when you make a change the code writes the list to the sheet which triggers the worksheet change code again when it is not needed.

See if this is any use. If it is not what you want you will have to try to clarify further. I have not been able to reproduce the error that you originally reported.

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
    ActiveSheet.Range("J6:J" & lastrow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ActiveSheet.Range("B" & lw + 5), Unique:=True
    Application.EnableEvents = True
  End If
End Sub

That is good work. But I am afraid i am not clear explaining the code or problem. Now, while pasting the unique values, you have suggested "B" & lw + 5. But when implemented that resulted in pasting the unique values continuously after the last row in B column i.e."B" & lw+5 and again next "B" & lw+5 and so on. But that is not what I wanted. I insert rows under the main data. And in that row I enter some relevant data including the column "J" value. Now that will be the lastrow. I want to extract unique values from column "J" and paste after 5 rows after the lastrow as just said. With this, I am getting the error repeated. I hope I am able to explain with some clarity.