+ 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 2013
    Posts
    82

    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
    5,066

    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 2013
    Posts
    82

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    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!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

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

    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 2013
    Posts
    82

    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 2013
    Posts
    82

    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
    5,066

    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 2013
    Posts
    82

    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
    5,066

    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 2013
    Posts
    82

    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
    5,066

    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 2013
    Posts
    82

    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
    5,066

    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 2013
    Posts
    82

    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
    5,066

    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. VBA/macro to split an excel file into multiple files with 500 lines each
    By karambos3 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-11-2023, 12:43 PM
  2. file split macro help
    By fgbdrum in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-11-2019, 03:46 PM
  3. Number clusters
    By shabazali1 in forum Excel General
    Replies: 9
    Last Post: 08-06-2018, 09:01 AM
  4. [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
  5. 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
  6. 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
  7. 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

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