1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

For Filtered Result Only-Each Unique Filtered Set Save as New file

Discussion in 'Ask an Excel Question' started by Chirag R Raval, May 31, 2017.

  1. Chirag R Raval

    Chirag R Raval Member

    Messages:
    241
    Dear All,

    There are many code available for each unique data set save as new file from Data
    via filter or advance filter but...till not found code for
    After Filter ..Only Result ... (in this result may have 3 or 4 unique value with set of same criteria rows) this result's each Unique set save as new file...

    in other word ...if you have database of 40 Salesman sales figures ...Each name's row about 40, 50 or 60 (Dynamic-Not fixed) you filter for only 4 salesman...after filter now...result shown for only that 4 salesman.. i required only this 4 filtered sales person's data separate Excel files ...

    please be clear i don't want files for all unique value of that columns ..i just want filtered result's unique data set as new file...

    i attached herewith sample file for filtered on 2 buyers that i want this result each filtered buyer's data as new excel file with headings remains in each file with auto save on fixed path..

    hope-your co-operation...

    Regards,

    Chirag Raval
  2. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    Hi,

    Assuming you will be manually filtering the data, try the following code:
    Code (vb):
    Sub SavetoNewFile()

        Dim lrow As Integer
     
        lrow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
     
        Workbooks.Add
        ThisWorkbook.Sheets("sheet1").Range("A1:S" & lrow).Copy ActiveWorkbook.Sheets(1).Cells(1, 1)

    End Sub
    Also attached
    After filtering the table, simply click the button and you will be presented with a new file containing only the visible results from the filter.
    You can add a new line of code if you wish to save the file automatically to a specific location... something like:
    Code (vb):
    ActiveWorkbook.SaveAs "D:\New folder\test.xlsx"
    Replace with the desired folder and file name (file name can be dynamic as well. Let me know if you need help with that).
  3. Chirag R Raval

    Chirag R Raval Member

    Messages:
    241
    Dear Sir,

    Thanks for your quick reply...
    your code work perfectly ..but please re-refer my question....if I filter on 2 buyer no out of many (like 11301 & 11305) I actually want 11301 as separate file & 11305 as separate file...not all mix result in one file...

    Regards,

    Chirag Raval
  4. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    Hi,

    I didn't fully understand the requirement at first, sorry about that.
    This should do it:
    Code (vb):
    Sub SavetoNewFile()

        Dim lrow, i As Integer
        Dim criteria As New Collection
       
        lrow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
     
        For Each c In ThisWorkbook.Sheets("Sheet1").Range("A2:A" & lrow)
            If c.EntireRow.Hidden = False And IsInCollection(c, criteria) = False Then
                criteria.Add c
                Debug.Print c
            End If
        Next c
       
        For i = 1 To criteria.Count
            ThisWorkbook.Sheets("Sheet1").ShowAllData
            ThisWorkbook.Sheets("sheet1").Range("A1:S" & lrow).AutoFilter Field:=1, Criteria1:=criteria(i)
            Workbooks.Add
            ThisWorkbook.Sheets("sheet1").Range("A1:S" & lrow).Copy ActiveWorkbook.Sheets(1).Cells(1, 1)
        Next i
       
    End Sub

    Private Function IsInCollection(valToBeFound As Variant, coll As Variant) As Boolean

        Dim element As Variant
       
        On Error GoTo IsInCollectionError: 'Collection is empty
       For Each element In coll
            If element = valToBeFound Then
                IsInCollection = True
                Exit Function
            End If
        Next element
       
        Exit Function
       
    IsInCollectionError:
        On Error GoTo 0
        IsInCollection = False

    End Function
     
    Attached
    Filter>>Click button
    Thomas Kuriakose likes this.
  5. Chirag R Raval

    Chirag R Raval Member

    Messages:
    241
    Dear Sir,

    Amazing...where are you before now?....this work perfect as I require..
    I always pray God for fulfil you life with joy ...Richness & many more joyful moments... I wander why not asking by anyone before me this question...
    (though I already research on this and another forums for this question...).

    May be this is first time world know ..perfect answerer for real requirement in the scope of "Separate file of filtered instance"...
    again many thanks ...you are really "Angel Of Code"

    if you wish ...to help..me & others... if we put filter requirement (Multiple Buyer Nos) in "Dialog box (Input Box)"...Raised by Code-without manually establish Filter on data...can it be possible? so our task become very fast...

    Hope your Little More -Co-Operation
    Regards,
    Chirag Raval
  6. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    Thanks for the kind words... I'm glad I was able to help you :)

    Regarding the option for inputting multiple Nºs, I would not recommend going with Input Box as it is quite limiting.
    Instead, you could use advanced filters. This is how it works manually:
    1.gif
    As you can see, you can have a list of Nºs and use it as the criteria for the filter :)

    Obviously this can also be automated. You would simple type the list of Nºs in a predefined range and then click the button... Done ;)
    Thomas Kuriakose likes this.
  7. Chirag R Raval

    Chirag R Raval Member

    Messages:
    241
    Dear Sir,

    Thanks...Again ...for your quick Guide ..you are again right....if our require is multiple which like a long list, & for perfection... we should write that on somewhere ...its better to write in excel...sheet ..(instead of dialog box)... (like your above example snippet) of course, we finally get what we desire...

    again many thanks ...for give me your valuable time & efforts..

    I hope this is helpful to millions of users who seriously work through Excel VBA ...

    Regards,
    Chirag
  8. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    Here you go then... added the advanced filter option.
    I've highlighted the range for the criteria (column "U") but you can keep adding more in "U4", "U5" and so on.

    Clicking the button will filter the table based on the criteria of this list and proceed as before :)
  9. Chirag R Raval

    Chirag R Raval Member

    Messages:
    241
    Dear Sir,
    Thanks again..but ...there are little bug...in code...though advance filter on 2 buyers & after filter display this 2 buyers . But...its generate files for all buyers may be freeze pan effect on result..?

    hope-your co-operation

    Regards,

    Chirag Raval
  10. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    I'm not sure what you mean... testing here it works correctly:
    1.gif

    As you can see, it filters 11306 and 11302 and creates files for these only!
    Please double check.
    Thanks
  11. Chirag R Raval

    Chirag R Raval Member

    Messages:
    241
    Dear Sir,

    I tested many times ..
    even restart my excel..all time its generate file for all buyers..
    I use Microsoft Professional Plus 2010...
    I can see in you animated snippet that you face 2 times some dialog box
    which I not.....
    I just Advance filter on 2 buyers as per your snippet ...displayed 2 buyers filtered ....& click Macro button but its generate 4 files for all unique buyers.

    please help..

    Regards,
    Chirag
  12. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    That is the problem, there is no need to do the advanced filter manually anymore... I've incorporated that part into the code as well.
    As long as you have the list of criteria, simply click the button, it will do all the rest (advanced filter included) automatically.

    You need to make sure the criteria in "U" are visible before clicking the button. When you run the filter manually you sometimes hide the criteria as well because they are on the same (now hidden) rows.
    Last edited: May 31, 2017
    Chirag R Raval likes this.
  13. Chirag R Raval

    Chirag R Raval Member

    Messages:
    241
    Dear Sir,

    Oh...Sorry for I can not understand ..(& may be you can not describe this portion in advance...before your this answer)..

    Amazing...Great work ...perfect...macro ...you just rocking..(& also for ..You save us from manually advance filter process )...this run like a charm..
    I really appreciate your work..

    Again

    Many thanks

    I pray to God for make you life more & more joyful & rich..& hope God raise your Ability, Efficiency & Power to do this types of little help who really..desire...& you also get back big rewards from that..helps..

    thanks again

    Regards,
    Chirag Raval
  14. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    Thank you
    I'm glad it is working :)
    Chirag R Raval likes this.
  15. Chirag R Raval

    Chirag R Raval Member

    Messages:
    241
    Dear Sir,

    Though I don't know anything about 2 aspects of VBA..."Collection And Dictionaries "

    I really feel wonder that Use of VBA Collection have Huge Scope...
    I also read about something "Dictionary", I see ..that currently may be its real use not in trend but may be recent future its in regular usage can be happen...

    for about pair of this 2 macros, how can I make this macro always available for any workbook? like make it "Globally available"

    Regards,
    Chirag raval
  16. Chirag R Raval

    Chirag R Raval Member

    Messages:
    241
    Dear Sir,

    may be you can help me ...
    for just Subtotalled 2 levels "Only Desired Buyers" Data as Separate file? code on that link work perfect but its generate for all buyers...

    how can your above code integrate with that code to generate Buyer Wise Subtitled data as separate file But..not for all buyers...Just Desired Buyers...

    http://www.chandoo.org/forum/thread...ique-subtotalled-instance-via-inputbox.34100/

    Hope your co-operation..

    Regards,
    Chirag Raval
  17. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    Hi,

    Info on that can be found here.
    Please take a look and let me know if you have any further questions.
    Thomas Kuriakose likes this.
  18. Chirag R Raval

    Chirag R Raval Member

    Messages:
    241
  19. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
  20. Chirag R Raval

    Chirag R Raval Member

    Messages:
    241
  21. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
  22. Chirag R Raval

    Chirag R Raval Member

    Messages:
    241
    Dear Sir,
    if you see & refer my below linked thread..about -subtotalled data-separate file buyer wise--
    http://chandoo.org/forum/threads/re...ique-subtotalled-instance-via-inputbox.34100/

    that code work perfect but for all buyers... I just need generate separate file only for required buyers. like you successfully demonstrate in my this running thread...

    I am ready to accept you auto-Advance filtered technique. Just put buyer no in a sheet & click the button ...subtotalled data's Separate files ..Ready for mentioned buyer no's as a possible solution for mentioned that thread...

    hope you understand..

    Regards,
    Chirag Raval
  23. Chirag R Raval

    Chirag R Raval Member

    Messages:
    241
    Dear All,

    Some notes

    THREAD-For Filtered Result Only-Each Unique Filtered Set Save as New file
    BASICS HELP PROVIDED BY CHANDOO.ORG--
    WORKING CODE PROVIDED BY "MR PCOSTA87" TO CHIRAG RAVAL THIS TASK SUCESSFULL THROUGH USE OF
    ""LOOP" & "COLLECTIONS"
    ORIGNAL THREAD STATED AT
    http://www.chandoo.org/forum/thread...h-unique-filtered-set-save-as-new-file.34572/
    " IT CAN BE WORK ON ANY FILE" HELPED TO COMPLETE BY "MR.KNARAYANAN"
    http://www.chandoo.org/forum/thread...xlsb-stuck-on-thisworkbook.34610/#post-206615

    Thanks
    Chirag Raval

Share This Page