+ Reply to Thread
Results 1 to 10 of 10

How to split worksheet into multiple worksheets based on column content

  1. #1
    Registered User
    Join Date
    06-03-2014
    Posts
    6

    How to split worksheet into multiple worksheets based on column content

    I'm trying to break up a worksheet into several worksheets based on the data in the "branch" column.
    I've tried to use other codes posted here, but, I can't seem to nail down the debug process and accurately identify the cells/columns that I need.
    I have a header row of 7 columns:
    member_id nps comments additional_comments Call Me! Number Branch

    the data in the "branch" column is the one I need to be the defining one for creating the new worksheets. How do I modify/create the module code?
    Last edited by Laurelmzitney; 06-03-2014 at 09:32 AM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2504 Win 11
    Posts
    24,684

    Re: Using a module

    Difficult to provide an answer on your code, if you have not provided it. Suggest you post your code and a sanitized copy of your spreadsheet. It is easier to provide a solution when we can actually see the problem.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    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

  3. #3
    Registered User
    Join Date
    06-03-2014
    Posts
    6

    Re: Code does not work

    Please Login or Register  to view this content.
    Last edited by Laurelmzitney; 06-03-2014 at 09:30 AM.

  4. #4
    Registered User
    Join Date
    06-03-2014
    Posts
    6

    Re: Using a module


  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Code does not work

    Hi

    Assuming the Branch column contains lots of duplicates then the first thing you need to do is extract a unique list of branches.
    If you have Excel 2010 then you can use the 'RemoveDuplicates' option after you have copied the range. Alternatively you could use an Advanced Data Filter to create a unique list

    e.g.

    Please Login or Register  to view this content.
    Then loop down the list starting in L1 with the following macro. Change the reference to Sheet1 as necessary

    Please Login or Register  to view this content.
    However before splitting data into several sheets I'd urge you to consider why you need to do this. I've lost count of the number of times people have started out with data that's spread across many sheets, (often sheets for different years, months or even days) and then found that summarising/analysing the whole lot of data is rendered very difficult.

    If you have a database and you periodically need to report specific branch information then the smart way is to use a separate sheet which is populated with Branch details with a Data Advanced Filter at run time.

    What is the overall aim here?
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  6. #6
    Registered User
    Join Date
    06-03-2014
    Posts
    6

    Re: Code does not work

    I believe I have corrected my errors. Sorry for the inconvenience. Thank you for your help.

  7. #7
    Registered User
    Join Date
    06-03-2014
    Posts
    6

    Re: Code does not work

    This excel doc is sent to me every week (like it is) and I have been "long-hand" copying the data to new worksheets; deleting the branches that I don't need on that worksheet; renaming the worksheet to match the branch; then once I have a worksheet for each branch, I insert a module that breaks the worksheets into individual documents. I was hoping to find an easier way to break up the data into the different worksheets, instead of the long-hand way.

  8. #8
    Forum Expert 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

    Let's try this one.
    Please Login or Register  to view this content.
    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.

  9. #9
    Registered User
    Join Date
    06-03-2014
    Posts
    6

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

    OUTSTANDING! Thank you so much! This will help me immensely!!!!

  10. #10
    Forum Expert 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

    You are welcome and thanks for the feedback.

    Pls, next time, be carefull with rules of the forum, because my intention IS NOT to be the bad quy here!!

+ 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. [SOLVED] Code locks cells when inserted in sheet module but returns error in standard module
    By yoda66 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-07-2014, 07:39 AM
  2. Replies: 1
    Last Post: 08-30-2011, 02:23 AM
  3. how to access Sheet module, normal module, Worbook module to type code
    By alibaba in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-31-2009, 07:51 AM
  4. Replies: 2
    Last Post: 03-27-2009, 11:48 AM
  5. Replies: 1
    Last Post: 04-10-2005, 07:07 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