Creating a copy of a Worksheet for each Autofilters checked

  • VBA Newbie here:


    Trying to do this with a Macro of course:


    I need to create a copy of a Worksheet "Software" that has several filters listed in column "A". However for each of the filters checked I need it to open in a new worksheet exactly like the "Software" sheet, but with it's own filtered results within the worksheet.


    So another words if I had


    • Software

      • Filter Criteria 1
      • Filter Criteria 2 (this is checked)
      • Filter Criteria 3
      • Filter Criteria 4 (this is checked)



    I need to have it so that when I run the macro it splits the Filters into it's own sheets like:

    • Software
    • Filter Criteria 2 (new sheet)
    • Filter Criteria 3 (new sheet)


    Hope that someone can help - I have tried lots of different things. I truly need to copy the entire sheet to keep the exact formatting, but keep the data filtered.


    Thanks so much for your help ahead of time!


    J

  • Re: Creating a copy of a Worksheet for each Autofilters checked


    An easy way to do this is to create a summary pivot table of your data.
    One of the default behaviors of a pivot table is that when you double click a value in the pivot table, it will open up a new worksheet with the values that were used to create the summary entry.


    http://www.mrexcel.com/archive/Pivot/ provides a good intro into pivot tables if you have not used them before.

  • Re: Creating a copy of a Worksheet for each Autofilters checked


    Quote from Kieran;755229

    An easy way to do this is to create a summary pivot table of your data.
    One of the default behaviors of a pivot table is that when you double click a value in the pivot table, it will open up a new worksheet with the values that were used to create the summary entry.


    http://www.mrexcel.com/archive/Pivot/ provides a good intro into pivot tables if you have not used them before.



    I don't think a Pivot Table will work for this scenerio - I am simply looking to duplicate the current sheet and filter by the results starting from the top and opening each filtered result in a new copied sheet. I need this to run in a macro as it's delivered to me different each time. Thanks for your help ahead of time. I find it hard no one has the answer to this one.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!