+ Reply to Thread
Results 1 to 17 of 17

Split a Worksheet into multiple Worksheets based off the DATA-SUBTOTAL

  1. #1
    Registered User
    Join Date
    01-12-2014
    Location
    Maple Grove, MN
    MS-Off Ver
    Excel 2013
    Posts
    7

    Question Split a Worksheet into multiple Worksheets based off the DATA-SUBTOTAL

    Hi!
    I'm brand new to this forum, but not so new with Excel, except when it comes to Macros and VBA. I run a report once a month that contains about 500 rows of data and I subtotal with the SUBTOTAL button under the DATA tab against a couple of columns. I have tried to split the main worksheet into multiple worksheets based on the subtotal, but when I have copied any code, it doesn't work well with the subtotal row for each unique value in the column. Is there any code, macro, or something else I can use to split this. My goal is to split it, and then save each individual worksheet as a separate PDF file. Right now, I am copying and pasting each group to a new workbook, and then saving that as a PDF. It's very tedious. If this is possible, is it also possible to have a special header for each individual worksheet. I have also attached a file with a master worksheet, and management worksheet. The master worksheet is before I subtotal my data in the management worksheet. Thank You!!Maintenance June 2013.xlsx

  2. #2
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Split a Worksheet into multiple Worksheets based off the DATA-SUBTOTAL

    Hi znpnh

    Welcome to the Forum!!

    What Subtotal do you wish the Worksheet to be split on? Client?
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Registered User
    Join Date
    01-12-2014
    Location
    Maple Grove, MN
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Split a Worksheet into multiple Worksheets based off the DATA-SUBTOTAL

    Yes, it would be on the Client Subtotal. Thank You!!

  4. #4
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Split a Worksheet into multiple Worksheets based off the DATA-SUBTOTAL

    Hi znpnh

    What about this...what should it be?
    have a special header for each individual worksheet.

  5. #5
    Registered User
    Join Date
    01-12-2014
    Location
    Maple Grove, MN
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Split a Worksheet into multiple Worksheets based off the DATA-SUBTOTAL

    I use the first 11 rows or so of the spreadsheet for my address, phone, and a logo. I attached the file here what the header is for all the split worksheets. I don't know it that will help?
    Attached Files Attached Files

  6. #6
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Split a Worksheet into multiple Worksheets based off the DATA-SUBTOTAL

    Hi znpnh

    Your (Owner) Maintenance (Month) 2014.xlsx Worksheet has a Column called R Hours and a Column called E Hours whereas your Master Sheet has only a Column called Hours...how do these relate?

    Edit...I see R and E in Column H (Type)...is this it?
    Last edited by jaslake; 01-15-2014 at 01:55 PM.

  7. #7
    Registered User
    Join Date
    01-12-2014
    Location
    Maple Grove, MN
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Split a Worksheet into multiple Worksheets based off the DATA-SUBTOTAL

    You would only be using the top 11 rows of the (Owner) Maintenance (Month) 2014 as header information for the other sheet. I can give you another file where now the columns match. Thanks!
    Attached Files Attached Files

  8. #8
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Split a Worksheet into multiple Worksheets based off the DATA-SUBTOTAL

    Hi znpnh

    Here's the process I envision:
    Start with Worksheet Master; Copy it to Worksheet Management
    With Worksheet Management, create the Sub Totals by Client
    With the Sub Totaled Worksheet Management, split off the Reports by Client.

    Where does your new Maintenance Report Query come from...how does it relate to Master? How, when is it created?

  9. #9
    Registered User
    Join Date
    01-12-2014
    Location
    Maple Grove, MN
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Split a Worksheet into multiple Worksheets based off the DATA-SUBTOTAL

    I bring in all the information from Access. I then sort and filter, adjust and subtotal as needed in the Master workbook in Excel. Once I am done with that, I open up the Workbook with the header and copy and paste each set of rows from the master sheet to the one with the header. I then save it as a PDF as it becomes an invoice for each individual client. I usually have a couple hundred to copy and paste each month and it becomes tedious. If I can separate the Master sheet into individual sheets within the same workbook with some sort of code that would bring in the "header" rows, I can easliy save each sheet as a PDF.

  10. #10
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Split a Worksheet into multiple Worksheets based off the DATA-SUBTOTAL

    So what should I be working with...your most recent upload?

  11. #11
    Registered User
    Join Date
    01-12-2014
    Location
    Maple Grove, MN
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Split a Worksheet into multiple Worksheets based off the DATA-SUBTOTAL

    Yes. The most recent upload and the second upload have the exact same columns. The second one just has the "header" information if that is even possible. Thanks!!

  12. #12
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Split a Worksheet into multiple Worksheets based off the DATA-SUBTOTAL

    Would you like the Code to create the PDF's after the split has been made?

  13. #13
    Registered User
    Join Date
    01-12-2014
    Location
    Maple Grove, MN
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Split a Worksheet into multiple Worksheets based off the DATA-SUBTOTAL

    If you can do that, that would be awesome!!

  14. #14
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Split a Worksheet into multiple Worksheets based off the DATA-SUBTOTAL

    I'll get back to you...

  15. #15
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Split a Worksheet into multiple Worksheets based off the DATA-SUBTOTAL

    Hi znpnh

    Haven't forgotten about you...have had other responsibilites...will get back to you ASAP...

  16. #16
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Split a Worksheet into multiple Worksheets based off the DATA-SUBTOTAL

    Hi znpnh

    In the attached Code, you'll be asked to select a Month and Year for the Report Date...this is used for the Naming Convention of the Client PDF. The PDF's are save to a Sub Folder called Reports. If the Sub Folder does not exist the Code creates it.

    Place these two workbooks in the SAME Folder. Open the Code Book and do your stuff.

    You'll also be asked which Maintenance Report you which to process. On my platform the Process takes about 3 and 1/2 minutes to complete. I'll see if there isn't a way to speed it up but no luck so far.

    Let me know of issues...
    Attached Files Attached Files

  17. #17
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Split a Worksheet into multiple Worksheets based off the DATA-SUBTOTAL

    Hi znpnh

    The Code in the attached runs in about 1 minute on my platform...
    Attached Files Attached Files

+ 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. How to split worksheet into multiple worksheets based on column content
    By jjexcels in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 11-05-2013, 11:42 AM
  2. [SOLVED] How to split worksheet into multiple worksheets based on column content
    By johnboytweed in forum Excel General
    Replies: 12
    Last Post: 10-16-2013, 12:01 PM
  3. Split worksheet content into multiple worksheets
    By macaco20 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-14-2012, 01:49 AM
  4. Split Worksheet into Several Worksheets using multiple columns
    By WBarrett in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-21-2011, 03:37 PM
  5. Split worksheet into multiple worksheets
    By ashtanga in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-12-2009, 04:43 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