+ Reply to Thread
Results 1 to 12 of 12

Split to Workbooks based on a Column followed by Worksheets based on another criteria

  1. #1
    Forum Contributor
    Join Date
    04-14-2017
    Location
    Auckland
    MS-Off Ver
    2016
    Posts
    119

    Split to Workbooks based on a Column followed by Worksheets based on another criteria

    Dear Experts

    Could you please help me out here to develop VB for:

    If Qty sold in Column R >6,

    Split those relevant rows (where Column R>6) in Workbooks by location (Column A) with the Columns C, D, E, R, AJ. Split relevant rows in Worksheets by Customer Names (Column D) inside those relevant workbooks (by location).

    I'd be so grateful if i can get this sorted. I have attached the sample file here.

    Cheers.

    Jared
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    04-14-2017
    Location
    Auckland
    MS-Off Ver
    2016
    Posts
    119

    Re: Split to Workbooks based on a Column followed by Worksheets based on another criteria

    B U M P - bump

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Split to Workbooks based on a Column followed by Worksheets based on another criteria

    What do you mean by split. Suggest you use a couple of your records where the value in Column R is greater than 6 and show us how the row should be split. From your current explanation I have no idea what you want your sheet to look like. I suspect that this is the issue that is not getting a response from anyone. Be as clear and precise as possible.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Split to Workbooks based on a Column followed by Worksheets based on another criteria

    Try
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    04-14-2017
    Location
    Auckland
    MS-Off Ver
    2016
    Posts
    119

    Re: Split to Workbooks based on a Column followed by Worksheets based on another criteria

    Sorry for that and apologise for not being clear.

    What i meant by that to create separate workbooks by location (Column A) with the relevant columns (as mentioned) and rows where the values of R are over 6 and then in those workbooks we need to create worksheets based on Customer name with relevant data. Please see the attached file that i would need to create (sample is for AKL location only) Workbooks should be named by Locations and the worksheets should be named by Customer names for that particular locations where the R values are over 6.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    04-14-2017
    Location
    Auckland
    MS-Off Ver
    2016
    Posts
    119

    Re: Split to Workbooks based on a Column followed by Worksheets based on another criteria

    Awesome Awesome Jindon, you're always great. Just one quick questions, can i add more column names to code if needed?

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Split to Workbooks based on a Column followed by Worksheets based on another criteria

    Yes,
    You need to change all
    Please Login or Register  to view this content.
    part in the code.

  8. #8
    Forum Contributor
    Join Date
    04-14-2017
    Location
    Auckland
    MS-Off Ver
    2016
    Posts
    119

    Re: Split to Workbooks based on a Column followed by Worksheets based on another criteria

    Thank you so much Jindon. You're awesome.

  9. #9
    Forum Contributor
    Join Date
    04-14-2017
    Location
    Auckland
    MS-Off Ver
    2016
    Posts
    119

    Re: Split to Workbooks based on a Column followed by Worksheets based on another criteria

    Sorry Jindon

    One more question to this:

    As i said last time that it should only count if Column R value is over 6, but i can see that Column K has same invoice numbers (IN numbers) with different quantities. Could you please set me a code here so that it would consider the same invoice numbers for a given customer and sum up all the quantities and then show only values over 6 like before together in the created worksheets under separate location based workbooks. Attached is the Sample data.

    Cheers and thanks for your help.
    Attached Files Attached Files

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Split to Workbooks based on a Column followed by Worksheets based on another criteria

    See if this is how you wanted.
    Please Login or Register  to view this content.

  11. #11
    Valued Forum Contributor
    Join Date
    11-28-2015
    Location
    indo
    MS-Off Ver
    2016 64 bitt
    Posts
    1,241

    Re: Split to Workbooks based on a Column followed by Worksheets based on another criteria

    Excelent
    GrandFather key >> parent key >> child key

  12. #12
    Valued Forum Contributor mohan.r1980's Avatar
    Join Date
    09-18-2010
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2010 (windows7)
    Posts
    729

    Re: Split to Workbooks based on a Column followed by Worksheets based on another criteria

    Nice Scripting Jindon Sir

+ 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. Split Excel Worsheets into new Workbooks based on Criteria
    By SwanDive in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-03-2017, 11:57 AM
  2. Replies: 9
    Last Post: 04-05-2016, 03:45 AM
  3. [SOLVED] Split Excel sheets to multiple Workbooks based on Criteria in Cell A1
    By mflank in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-10-2016, 09:58 AM
  4. split single spreadsheet into multiple workbooks based on value in column D
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-22-2014, 07:27 PM
  5. Split Worksheet into Workbooks based on a column value
    By Amarjeet Singh in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-09-2014, 07:09 AM
  6. [SOLVED] How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column
    By UzieJacuzzi in forum Excel Programming / VBA / Macros
    Replies: 30
    Last Post: 07-31-2012, 07:26 AM
  7. [SOLVED] Split table, based on two criteria, into separate workbooks
    By gingert88 in forum Excel General
    Replies: 6
    Last Post: 07-10-2012, 06:08 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