+ Reply to Thread
Results 1 to 13 of 13

How to split worksheet into multiple worksheets based on column content

  1. #1
    Registered User
    Join Date
    07-13-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2010
    Posts
    8

    How to split worksheet into multiple worksheets based on column content

    Just wanted to say hello to the forum. I've never posted before, but I have read several forums in the past as I work on spreadsheets quite often.
    I wanted to see if anyone knew of an efficient way to split a worksheet into multiple worksheets based upon column content. I've searched the web and I haven't been able to find any content that helps me directly.
    I would basically have unique accounting codes in "Column A" and all the content under that accounting code would then go into a new worksheet ideally. I have over fourteen thousand lines of data so doing it by hand would be time consuming.
    This spreadsheet would just contain raw data. It wouldn't have spaces anywhere and it isn't formatted in any way. I didn't attach the spreadsheet because it's pretty basic. I can certainly do that if it is necessary.

    Any suggestions out there?

    Thanks in advance!

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,134

    Re: How to split worksheet into multiple worksheets based on column content

    Yes, it would help if you could attach a sample file (doesn't need to contain 14000 rows !!). How many different accounting codes (and therefore sheets) do you think you will need?

    Pete

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,988

    Re: How to split worksheet into multiple worksheets based on column content

    http://www.excelforum.com/excel-gene...-criteria.html

    Maybe like the file in the above link.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Registered User
    Join Date
    07-13-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: How to split worksheet into multiple worksheets based on column content

    I can certainly attach the file. I actually have one additional request to the prior one and that is running a subtotal on each sheet after it is broken out into a separate worksheet.
    I thought I could do this all at once by making every worksheet active, but Excel "grays out" that option.

    Anyway, here is a sample of the spreadsheet. I took out most of it (twelve thousand something lines), but you will get the idea. I will probably need twenty five to maybe forty different tabs.
    Column A could have anywhere from one to fifty or more entries. I just wanted to separate them into separate workbooks with subtotals, and maybe a way to identify them on their tab.

    I will need to do this every month so having a macro will save a lot of time!

    Thank you in advance!
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,988

    Re: How to split worksheet into multiple worksheets based on column content

    Run the macro.

    I did not know how to add the total on each sheet.

    The code is from Ron de Bruin.

  6. #6
    Registered User
    Join Date
    07-13-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: How to split worksheet into multiple worksheets based on column content

    Thank you oeldere for the response. In between responses I found this macro that worked. It might be the same as the one you posted. After running it though I realized that I really need to have a subtotal somehow added to each spreadsheet. If anyone can help with that I would be very grateful. If not then I can say that this thread is complete and the original question was solved.

    Please Login or Register  to view this content.
    Last edited by arlu1201; 07-14-2012 at 06:48 AM. Reason: This post needed code tags.

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,988

    Re: How to split worksheet into multiple worksheets based on column content

    If you add code please add tags # around it.

    For the subtotal you can also use pivot table (see example).

    There are more options, but it just to show you the possebility.
    Last edited by oeldere; 07-13-2012 at 03:47 PM. Reason: If you add code please add tags # around it.

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,134

    Re: How to split worksheet into multiple worksheets based on column content

    Quote Originally Posted by johnboytweed View Post
    I will need to do this every month so having a macro will save a lot of time!
    It could be done with formulae but you would need to create the sheets yourself, so you are better off with a macro solution.

    Pete

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,988

    Re: How to split worksheet into multiple worksheets based on column content

    Maybe you can reply to my answer of an pivot table.

  10. #10
    Registered User
    Join Date
    07-13-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: How to split worksheet into multiple worksheets based on column content

    I think the pivot table could work, but as you stated there are several options. In this instance I think this macro complete the task the quickest:
    Please Login or Register  to view this content.
    Thank you so much for your help!

  11. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,988

    Re: How to split worksheet into multiple worksheets based on column content

    Glad I could help. Thanks for the reply.

  12. #12
    Registered User
    Join Date
    08-09-2012
    Location
    NYC and CT
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    20

    Re: How to split worksheet into multiple worksheets based on column content

    Hi Oeldere,

    I found this thread and the macro works great for me! I know this thread is old but it's worth a try. How can I alter this macro to include the first 5 rows as my header for each sheet? Currently it just takes the headers in row A, but if I would like to include the 4 rows above the headers on each sheet. Thank you!

    Mark

  13. #13
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: How to split worksheet into multiple worksheets based on column content

    mmdecarl

    Unfortunately you have to start your own thread for this as per forum rules.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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