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.

Require to save separate file in fix path for each unique subtotalled instance via inputbox

Discussion in 'Ask an Excel Question' started by Chirag R Raval, Apr 21, 2017.

  1. Chirag R Raval

    Chirag R Raval Member

    Messages:
    140
    Dear All

    Sub:-Need to generate & save separate file for each unique subtotalled instance
    if put require instance in input box, (input box can take 1 or more then one instance )
    or it ask for save separate file for all unique instance in fixed path directory (Folder)


    I have a file

    subtotalled on first main criteria

    then also subtotalled another criteria for it main criteria (2 levelled subtotal-for each instance)

    I require separate file for each unique main first subtotalled instance
    with all data (with all 2nd level subtotal) preserved with all formatting & heading.

    purpose -Just extract & mail for that instance

    any one can help??
  2. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,580
    Hi, Chirag R Raval!
    Consider posting the sample file and write down the desired output.
    Regards!
  3. Chirag R Raval

    Chirag R Raval Member

    Messages:
    140
    Dear Sir,
    Attached my sample file
    with requirement-

    (1) Input box for put Required Buyer No (May be 1 or more )
    in it Each Instance of Subtotalled Party
    generate as separate file & auto save on fixed path
    with all formatting, 2nd level subtotal & heading preserved.

    Purpose-Mail for that party

    Hope-your co-operation

    Regards

    Attached Files:

  4. Chirag R Raval

    Chirag R Raval Member

    Messages:
    140
    Dear Sir,

    Can you help me??

    regards
  5. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,580
    Hi, Chirag R Raval!

    I don't know, please check the attached file and verify if it works.
    This is the code for the required macro:
    Code (vb):

    Option Explicit

    Sub DropkickMurphys()
        ' constants
       Const ksWSMain = "Sheet1"
        Const ksMain = "A:E"
        Const ksPath = "Per Buyer"
        Const ksWBBuyer = "Buyer #"
        Const ksControl1 = "Total"
        Const ksControl2 = "Grand "
        ' declarations
       Dim rng As Range, cel As Range
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim lAnt As Long, lAct As Long, lFile As Long, sFolder As String
        ' start
       Set rng = Worksheets(ksWSMain).Range(ksMain)
        sFolder = Format(Now(), "yyyymmddhhnnss")
        MkDir ThisWorkbook.Path & "\" & sFolder
        lFile = 0
        lAnt = 1
        lAct = 0
        ' process
       With rng
            Set cel = .Columns(1).Find(ksControl1, .Cells(lAnt, 1), xlValues, xlPart)
            Do
                If Not cel Is Nothing Then
                    lAct = cel.Row
                    lFile = lFile + 1
                    '
                   Set wb = Workbooks.Add
                    rng.Rows(1).Copy wb.Worksheets(1).Cells(1, 1)
                    Range(.Rows(lAnt + 1), .Rows(lAct)).Copy wb.Worksheets(1).Cells(2, 1)
                    wb.SaveAs ThisWorkbook.Path & "\" & sFolder & "\" & _
                        Left(.Cells(lAct, 1).Value, InStr(.Cells(lAct, 1).Value, " ") - 1) & ".xlsx"
                    wb.Close (False)
                    Set wb = Nothing
                    '
               Else
                    lAct = 0
                End If
                lAnt = lAct
                Set cel = .Columns(1).Find(ksControl1, .Cells(lAnt, 1), xlValues, xlPart)
            Loop Until lAct = 0 Or cel.Value = ksControl2 & ksControl1
        End With
        ' end
       MsgBox CStr(lFile) & " files created in subfolder " & sFolder, vbInformation + vbOKOnly, "Summary"
        Set cel = Nothing
        Set rng = Nothing
    End Sub
    Regards!

    Attached Files:

  6. Chirag R Raval

    Chirag R Raval Member

    Messages:
    140
    Dear Sir JB7,

    Great Sir,
    many thanks for your effort for me...
    your code run amazingly .......& any required small formatting that I can done

    I believe that its very important & very helpful in work for millions user world wide...

    but one Small point missing that (most required) is "InputBox" in which we can give 1 or more buyer no & Code run for only that buyers ...

    hope your Co-Operation...

    Regards,

    Chirag
  7. Chirag R Raval

    Chirag R Raval Member

    Messages:
    140
    Dear Sir,

    There are ( should be ) need 2 options in Input box

    (1) particular buyer Numbers can be enter by keyboard by user as per requirement.

    (2) if user want , can be generate Separate file for all buyer in One process.

    hope your co-operation.

    Regards,

    Chirag
  8. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,580
    Hi, Chirag R Raval!
    Input Box would be suitable for generating a separate file for only one buyer, but not for more than one in a single process.
    For this you should add a column to the worksheet where you have the buyers list (which isn't in the uploaded workbook) and then include an If...EndIf pair o instructions to check if the file for that customer should be created, before and after the following part of the macro code.
    Code (vb):

                    Set wb = Workbooks.Add
                    rng.Rows(1).Copy wb.Worksheets(1).Cells(1, 1)
                    Range(.Rows(lAnt + 1), .Rows(lAct)).Copy wb.Worksheets(1).Cells(2, 1)
                    wb.SaveAs ThisWorkbook.Path & "\" & sFolder & "\" & _
                        Left(.Cells(lAct, 1).Value, InStr(.Cells(lAct, 1).Value, " ") - 1) & ".xlsx"
                    wb.Close (False)
                    Set wb = Nothing
    Regards!
    Chirag R Raval likes this.
  9. Chirag R Raval

    Chirag R Raval Member

    Messages:
    140
    Dear Sir,

    This is final requirement (Final part) of whole this thread.... if we can full fill this requirement whole process cleared & re-usable forever...

    if available drop down menu in current file, so we can direct reach that buyer from keyboard to tick in check box in front of that buyer no, also can select multiple buyer to generate separate file.. & if we select "All" in this box All buyer's separate file generate In folder.

    hope your co-operation...

    Regards,

    Chirag
  10. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,580
    Hi, Chirag R Raval!
    Then upload the same updated file with the worksheet where you have the buyers list in order to provide the necessary data that people who might help you would require.
    Regards!
  11. Chirag R Raval

    Chirag R Raval Member

    Messages:
    140
    Dear Sir,
    Same file attached with screen shot what is require.
    I just ON auto filter & dropdown on Buyers we can select there either "All"
    or any instance to separate file for that instance.

    Hope your co-operation
    Regards,
    Chirag

    Attached Files:

  12. Chirag R Raval

    Chirag R Raval Member

    Messages:
    140
    Dear Sir,

    Code Run Perfectly...
    Just Droop Down menu require for separate required instance as new file..

    Regards

    Chirag
  13. Chirag R Raval

    Chirag R Raval Member

    Messages:
    140
    Dear Sir,

    can small cade available for this small & final-Complete macro?
    Regards,
    Chirag
  14. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,580
    Hi, Chirag R Raval!
    I asked:
    You answered:
    The file is the same as my last uploaded, you only included a printscreen of Data Filter dialog for buyers column. Had looked at the info displayed (or better, have tried the provided macro with the filtered worksheet) would have noticed that it's not suitable for your requirements.
    That's why I asked for a "worksheet where you have the buyers list", i.e.:
    Buyer ID Buyer name
    2 Ken Casey
    3 Matt Kelly
    5 Al Barr
    7 James Lynch
    11 Tim Brennan
    13 Jeff DaRosa
    Regards!
  15. Chirag R Raval

    Chirag R Raval Member

    Messages:
    140
    Dear Sir,

    Attached as per your requirement....Excel file ..with whole buyer list
    please look Column Heading "New" for which this whole macro invented.


    One small request that if Macros requirement for 2 files to complete the process, please arrange your macro read this file (for Take reference) as background on particular fixed path (AS HIDDEN MODE) , so we don't need to open "Buyer Master" for run this macro (& it will be always available & Re-Usable-Forever).

    Hope-Your Co-Operation.

    Regards,

    Chirag

    Attached Files:

  16. Chirag R Raval

    Chirag R Raval Member

    Messages:
    140
    Dear Sir,

    Hope there are some way to complete this process.

    regards,

    Chirag
  17. Chirag R Raval

    Chirag R Raval Member

    Messages:
    140
    Dear All,
    Can anyone help here?

    Regards,
    Chirag
  18. Chirag R Raval

    Chirag R Raval Member

    Messages:
    140
    Dear All,

    just select the buyer from "Select from Drop Down" OR "Put Buyer No through keyboard in input box" OR generate new file ....

    please help.

    Regards,
    Chirag
  19. Chirag R Raval

    Chirag R Raval Member

    Messages:
    140
    Dear All,

    If within excel , we can select multiple instance from drop down list & excel displayed that records as result,

    & if we give Advance filter, we can copy that records to another sheet…

    Its is possible this via VBA?, for only selected instance all records we can save as through VBA?

    Is there are any limitation of Excel VBA? Is EXCEL VBA can do everything which Excel can do..?

    Meaning that In Excel’s Basic Facility given By Microsoft, we can not reach at that point. Via VBA..

    Please explain…


    Regards,

    Chirag
  20. Marc L

    Marc L Excel Ninja

    Messages:
    3,025
    Hi !
    Sky is the limit ! Almost all can be done via VBA (+95%)
  21. Chirag R Raval

    Chirag R Raval Member

    Messages:
    140
    Dear Sir/All


    Thanks...for inform to all "Sky is the limit" VIA VBA...


    I am not know VBA Well..Just Start to learn...

    but regarding my question. My thinking for steps on it may be


    1. Create Array via Code to store Each Unique Value of All Buyer No in it

      & display that to user For by default All Selected (for which above code already do that job) OR user can select multiple Ramble from that array…There are cancel button also in that dialog box to cancel whole process… if user “Select All” your already working code do that job…& hence not require below all steps…


    2. If user select multiple random …from 1st Array


    3. Another code generate 2nd array based on first ..for contains –Hold only selected values in it..

    4. Now above code (Do…Loop….Until---) run for Every elements of 1st Array OR for 2ND Array


      That’s it…& may be there are not require full buyer master as separate file…

      Due to code direct store unique buyer Numbers in 1st Array from current file


      I Am not sure about ..but …I think can be this way we can full fill our requirement.


      Hope your all’s co-operation.


      Regards,


      Chirag
  22. Chirag R Raval

    Chirag R Raval Member

    Messages:
    140
    Dear All,

    Can there are scope to generate files for selected buyers?
    can anyone help regarding this?

    hope your co-operations

    Regards,
    Chirag
  23. Chirag R Raval

    Chirag R Raval Member

    Messages:
    140
    Dear All,

    I am Ready to accept your solution


    I already submitted Buyer Master for your requirement..
    I just ready to leave concept of "Input Box"
    I am ready for your solution .
    I just want desire buyers separate file..

    Hope your Co-Operation
    regards,
    Chirag
  24. Chirag R Raval

    Chirag R Raval Member

    Messages:
    140
    Dear All,

    I already submitted Buyer Master for your requirement..
    I just ready to leave concept of "Input Box" ", Drop Box"
    I am ready for your solution .
    I just want desire buyers separate file..
    As per your suggestion...regarding this Thread ...

    Hope your All's Co-Operation
    regards,
    Chirag Raval
  25. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    Hi,

    See if the attached file does it for you.
    Notice that the empty cells in column A also need to have the Buyer nº for it to work. I've added them in the month subtotal rows and painted the text white so it doesn't show up (see cells "A5" or "A8" as an example).

    Let me know if this is what you wanted.

    Attached Files:

Share This Page