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:
    29
    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,416
    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:
    29
    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:
    29
    Dear Sir,

    Can you help me??

    regards
  5. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,416
    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:
    29
    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:
    29
    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,416
    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!
  9. Chirag R Raval

    Chirag R Raval Member

    Messages:
    29
    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,416
    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:
    29
    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:

Share This Page