• 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...

  • 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

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
 

Attachments

PCosta87

Well-Known Member
Hi,

Assuming you will be manually filtering the data, try the following code:
Code:
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:
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).
 

Attachments

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
 

PCosta87

Well-Known Member
Hi,

I didn't fully understand the requirement at first, sorry about that.
This should do it:
Code:
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
 

Attachments

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
 

PCosta87

Well-Known Member
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
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 ;)
 
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
 
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
 
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
 

PCosta87

Well-Known Member
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
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:
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
 
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
 
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/threads/require-to-save-separate-file-in-fix-path-for-each-unique-subtotalled-instance-via-inputbox.34100/

Hope your co-operation..

Regards,
Chirag Raval
 

PCosta87

Well-Known Member
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
Hi,

Info on that can be found here.
Please take a look and let me know if you have any further questions.
 
Hi,

It is working now.
There are a lot of comments there already... can you tell me exactly what it is that you wish me to help with?

Thanks
Dear Sir,
if you see & refer my below linked thread..about -subtotalled data-separate file buyer wise--
http://chandoo.org/forum/threads/require-to-save-separate-file-in-fix-path-for-each-unique-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
 
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/threads/for-filtered-result-only-each-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/threads/after-move-macro-from-saved-file-to-personal-xlsb-stuck-on-thisworkbook.34610/#post-206615

Thanks
Chirag Raval
 
Top