Filter the results and save them to a new workbook in VBA

  • Hello

    I hope all of you are doing well. I am trying to write code but failed . My sample file is attached .

    Result file (Commission) is attached for reference.


    I want to do below

    1: Add a column After C Column

    2: Lookup C column Data from "Product IDs" sheet in Column D


    3: Filter the Without #N/A Data from Column D

    4: Filter the Commission Amount less than zero from column E

    4: Select the Completed Filtered Data from Column A to E Paste Special to a New Workbook and Save it with Name " Commission"

    Many many thanks in advance

  • What are you looking up from Product IDs? There's only one column of data

    I want to Filter only Those Products which are available in sheet(Product ids) , once available ids filtered then filter the less than zero amount from commission column and export the complete filtered data to a new workbook.

    Please note this is just sample I have more than 10000+ rows data

  • You can record a macro whilst doing it manually as you have for the video. The code will need tidying up afterwards, but post the code here and I'll take a look

    Record a macro

  • Try this


  • THanks for this code but I am getting this error

    Run Time Error 438:

    " Object does not support this property or method "


    error is on this line

    .AutoFilter.Range.Offset(1, 0).Resize (.AutoFilter.Range.Rows.Count - 1)

    Try this


  • This should work