Filtering and copy pasting to new workbooks

  • Hi,


    I need to filter a column and copy paste results into a new workbook until all unique values in that column has been filtered for. I am receiving a 'subscript out of range' error on this line:


    Code
    1. last = Workbk.Sheets(sht).Cells(Rows.Count, "L").End(xlUp).Row


    This is the full code:

    (I saved this code in my personal workbook and would like to run it on the active workbook)

  • Hi,


    You could replace the instruction :


    Set Workbk = ThisWorkbook


    by


    Set Workbk = ActiveWorkbook


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • You need to understand the difference.


    ActiveWorkBook is the workbook that is active when the code runs.


    ThisWorkBook is the workbook that holds the code.

  • Realised my mistake with ActiveWorkBook shortly after posting my code there. I seem to have a couple other issues though. I have data from Column A to BD on my source file, where I need to filter through Column L and copy the results from Column A to BD to a new workbook, and repeat for each unique value in Column L. It is not working as I am hoping for at the moment.

  • Glad to hear you have made some progress ..;)


    As far as your initial objective of 'Exploding Unique items', you should be posting a tiny sample file ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • This is code from the recorded macro, though I am looking for cleaner code that loops through all the unique values from the column to be filtered, hence my attempt at the code posted above.

  • Try this. It currently assumes the column with the values in is 3. You will need to amend for your use.


    It saves the new workbooks to a subfolder in the same directory as the master workbook.


    I can provide code to browse for the destination folder if required, or you can hard code a different path


  • Hi Roy,


    I am getting an error in this line, have changed it to 12 as that is the column I need filtered.

    Code
    1. rData.Columns(12).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Cells(1, .Columns.Count), Unique:=True
  • What error are you getting?


    If you want to attach a small example workbook I'll take a look.


    I'll be unavailable for an hour or so, but I'll check back later.


    If you and all users are using Microsoft 365 then we can update that line.

  • Hello again,


    Have you tested


    Hope this will help


    P.S. This will create your Individual sheets ... Then it is pretty easy to create a New Workbook for each New Worksheet ...:)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Once all your individual sheets are generated by the Explode macro ...


    you can create workbooks for each worksheet as follows :



    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Once you have tested the macros ... feel free to share your comments ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim, just tried it and it works perfectly. The CopyToRange line wasn't working for me but copying to sheets first works out better. Thank you!


    Glad to hear you could sort it out :)


    Thanks for your Thanks !!!

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • My code copies to sheets then moves the sheet to a new workbook. Maybe you could answer my questions about the error, this has been working for many years.