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

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

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??
 

SirJB7

Excel Rōnin
Hi, Chirag R Raval!
Consider posting the sample file and write down the desired output.
Regards!
 
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
 

Attachments

SirJB7

Excel Rōnin
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:
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!
 

Attachments

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

SirJB7

Excel Rōnin
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:
                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!
 
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
 

SirJB7

Excel Rōnin
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!
 

SirJB7

Excel Rōnin
Hi, Chirag R Raval!
I asked:
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.
You answered:
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.
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!
 
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
 

Attachments

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
 
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
 
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
 
Dear All,

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

hope your co-operations

Regards,
Chirag
 
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
Dear All,

I am Ready to accept your solution

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:
                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!

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

PCosta87

Well-Known Member
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.
 

Attachments

Top