Copying unique value in a column named range to a range in same worksheet in worksheet change event

  • Hello Expert,


    I am trying to copy the unique value from a dynamic named range to a range in a worksheet using advancedfilter method every time when value is changed or new data in added to named range. I am facing two problems.


    1. Every time when new data is entered in named range : 'Test', it gives me runtime error i.e. runtime error28 : Out of stack space
    2. In filtered result, I want to copy a sum of data corresponding to unique value of named range 'Test' present in column 'Price' of the table as shown in attached workbook. Any lead to achieve it would be helpful.

    Please help me resolve this error, and achieve above functionality. Thanks in advance:)


  • You are changing the sheet within the change event. This will cause the event to run again, which then changes the sheet & runs the code again.

    In other words, you have created a permanent loop.

    You need to stop events from running like

    Code
    1. Application.EnableEvents = False
    2. Sheet1.Range("k2") = count
    3. '----------------copying unique values to a range-------------------
    4. Target.AdvancedFilter Action:=xlFilterCopy, copytorange:=Sheet1.Range("K7"), unique:=True
    5. Application.EnableEvents = True

    Also you should never use VBA keywords such as Target for your own variables.

  • Thanks for your reply. It's working fine now.


    But I have a problem in advanced filter. It is copying duplicated data from named range. I see that 1st row data of named range "test" is compulsorily getting copied as shown in below workbook. Please help.


    Also, I would like to add the 'Price' column for all unique value in code column and display it in filtered range. How do I do that.

  • Ok, how about

  • Thank you so much Fuff13 for introducing me to this concept. Very helpful.


    There is one doubt I would like to clear, I get an error: "Runtime Error '1004' : Method 'Range' of object '_worksheet' failed." whenever there is no value in the named range and I try to enter first value. Please let me know how I can resolve such error. Is there any error handling method for it!

  • How about

  • Thank you so much. How can we restrict the worksheet_change to run only when 'code' and 'price' column value changes.


    I tried putting if statement in line 2 like


  • How about