+ Reply to Thread
Results 1 to 16 of 16

Macro to Split the file according to clusters

  1. #1
    Registered User
    Join Date
    08-21-2009
    Location
    Chennai,India
    MS-Off Ver
    Excel 2003
    Posts
    78

    Post Macro to Split the file according to clusters

    Hi All,

    I need to automate a excel sheet having list of FTE working for the different clusters and send the reports via mail.

    In my team around 10 people extracting the same report and sending it to the stakeholders based on the cluster.
    Whereas instead of 10 people extracting the report separately one person can extract the report from the system and split it according to the cluster and send it to each team member and then they can send it to their stake holders.

    Herewith have attached the dummy report for refrence.

    I am in need of VBA code where we need to split the report based on the cluster on the Column A.

    Thanks in Advance
    Aravind
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,492

    Re: Macro to Split the file according to clusters

    split the report based on the cluster on the Column A.
    How do you see the result:
    One sheet per cluster, with sheet's name the same than the cluster ?
    One file per clusterr, with File's name the same than the cluster, recorded in the same folder ?
    - Battle without fear gives no glory - Just try

  3. #3
    Registered User
    Join Date
    08-21-2009
    Location
    Chennai,India
    MS-Off Ver
    Excel 2003
    Posts
    78

    Re: Macro to Split the file according to clusters

    Hi ,

    Thanks for your response. Yes I require one file per cluster, with file name the same as the cluster in the same folder where we have the master data.

    Thanks

    Aravind

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,036

    Re: Macro to Split the file according to clusters

    Administrative Note:

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,492

    Re: Macro to Split the file according to clusters

    aravindkm,
    The macro is ready and waitting for you to follow moderator's comment
    PCI

  6. #6
    Registered User
    Join Date
    08-21-2009
    Location
    Chennai,India
    MS-Off Ver
    Excel 2003
    Posts
    78

    Re: Macro to Split the file according to clusters

    Hi,

    Apologies for the inconvenience.

    I have posted the query in excelguru forum.

    Please find below the link.

    https://www.excelguru.ca/forums/show...a-column-value

    Apologies once more will provide the link goingforward if i post the query in other forum.

    Thanks

    Aravind

  7. #7
    Registered User
    Join Date
    08-21-2009
    Location
    Chennai,India
    MS-Off Ver
    Excel 2003
    Posts
    78

    Re: Macro to Split the file according to clusters

    Hi PCI,

    Apologies for cross posting the same query.

    Have followed the moderator's comment and posted the link below.

    Thanks
    Last edited by aravindkm; 11-18-2019 at 12:29 AM.

  8. #8
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,492

    Re: Macro to Split the file according to clusters

    See next code and file attached
    Please Login or Register  to view this content.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    08-21-2009
    Location
    Chennai,India
    MS-Off Ver
    Excel 2003
    Posts
    78

    Re: Macro to Split the file according to clusters

    Thanks for the code PCI.

    If I want to split the file based on PL5 is it possible.

    Sorry for the inconvenience.

    Thanks

    Aravind

  10. #10
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,492

    Re: Macro to Split the file according to clusters

    See next code.
    You can modify the column in reference, adjusting: Const WkCol = 13

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    08-21-2009
    Location
    Chennai,India
    MS-Off Ver
    Excel 2003
    Posts
    78

    Post Re: Macro to Split the file according to clusters

    Hi PCI,

    Thanks for the code again

    Sorry for the inconvenience.

    Please find attached the Spreadsheet for which we need to split the file for.

    I want the COL C to be split in to the separate file.

    When i tried running the macro i am getting the overflow error .

    Attached the screenshot of the error also.

    The number of rows in the file is around 1,00,000.

    Thanks
    Attached Files Attached Files

  12. #12
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,492

    Re: Macro to Split the file according to clusters

    See here a remake of the macro ... the origunal file was very welcome ...!
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    08-21-2009
    Location
    Chennai,India
    MS-Off Ver
    Excel 2003
    Posts
    78

    Re: Macro to Split the file according to clusters

    Wow it was working fine. Thanks a lot for your help.

    Thanks again PCI.

  14. #14
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,492

    Re: Macro to Split the file according to clusters

    Good news, it took time but we succeeded.
    I'm a bit surprise it does not take so long to treat so many rows.
    You're welcome and have look on the reputation !!!
    Enjoy Excel

  15. #15
    Registered User
    Join Date
    08-21-2009
    Location
    Chennai,India
    MS-Off Ver
    Excel 2003
    Posts
    78

    Re: Macro to Split the file according to clusters

    Thanks PCI. It really took time but thanks to you.

    One more question I am not able to assign the macro to a button in a separate sheet in excel 2013 and 2016 as well trying for that now.

    Could you please help.

    Thanks

    Aravind

  16. #16
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,492

    Re: Macro to Split the file according to clusters

    Personally, i don't like button and use TexBox:
    Prepare the TextBox => Insert Text> TextBox
    Right clic the TextBox > Assign Macro
    In which sheet you want to put the button ?
    Attached Files Attached Files
    Last edited by PCI; 11-19-2019 at 07:18 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. file split macro help
    By fgbdrum in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-11-2019, 03:46 PM
  2. Number clusters
    By shabazali1 in forum Excel General
    Replies: 9
    Last Post: 08-06-2018, 09:01 AM
  3. [SOLVED] Macro to organize information within clusters
    By tisahardknocklife in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-26-2017, 10:05 PM
  4. Macro or function to split CSV file contain 2 million lines
    By is2_egypt in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-15-2016, 05:36 PM
  5. Macro to split one file into many based on criteria !!!
    By gamefreak in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-11-2015, 05:15 PM
  6. Need Macro file to split then consolidate workbooks based on partial file name
    By dawnmau in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-04-2014, 04:37 PM
  7. VBA/macro to split an excel file into multiple files with 500 lines each
    By karambos3 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-13-2013, 05:55 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1