Closed Thread
Results 1 to 25 of 25

How to split worksheet into multiple worksheets based on column content

  1. #1
    Registered User
    Join Date
    06-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    15

    How to split worksheet into multiple worksheets based on column content

    Hi,

    I would like to take the contents of a column and split it into multiple worksheets.

    example Column b has values of Pa,GT,CR

    I would like to automatically create 3 tabs(worksheets) PA,GT,CR.

    thanks in advance!

  2. #2
    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: How to split worksheet into multiple worksheets based on column content

    Hi,

    One way assuming your names are in a contiguous list in Sheet1 column A with nothing in column B. i.e. the .CurrentRegion will accurately count the list of cells in column A

    Please Login or Register  to view this content.
    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.

  3. #3
    Registered User
    Join Date
    06-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    15

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

    I have data in most of the columns. Data is within A,b,c,d,e,f,g ,but I want to sort and create a worksheet by using the contents of Column B.
    Would the solution you provide do that?

  4. #4
    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: How to split worksheet into multiple worksheets based on column content

    Yes,

    Just modify to

    For x = 1 To Sheet1.Range("Bf:Bl").Cells.Count
    Sheets.Add.Name = Sheet1.Range("Bf").Cells(x,1)
    Next x

    where f and l are the first and last row numbers in column B which contain your sheet names.

  5. #5
    Registered User
    Join Date
    06-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    15

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

    ok It sounds good now what do I do with the info you provided me?

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

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

    Try this code
    Please Login or Register  to view this content.
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
    Choose Insert | Module
    Where the cursor is flashing, choose Edit | Paste

    To run the Excel VBA code:
    Choose View | Macros
    Select a macro in the list, and click the Run button
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  7. #7
    Registered User
    Join Date
    06-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    15

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

    Sorry Still trying to get my bearings with this. I'm getting an error"Run-time error '9': Subscript out of range"

    I get that error for Arlu1201's code

    Richard. For your first code I got the same error above,but for your edit code
    I get Run-time error '1004':
    Method 'Range' of Object'_Worksheet'failed
    Last edited by jjexcels; 10-10-2013 at 01:01 PM.

  8. #8
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

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

    What is the name of the sheet containing the data?

    Are there any blank cells in column B?

  9. #9
    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: How to split worksheet into multiple worksheets based on column content

    Hi,

    Copy and paste the actual code you are using so we can check.

    And can you confirm that you are using the VBA sheet CODE name. My example used 'Sheet1' but of course yours may be different. And don't confuse the VBA sheet CODE name with the Excel sheet TAB name. The two may be the same but they may also be different.

  10. #10
    Registered User
    Join Date
    06-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    15

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

    OK.not sure what I was doing wrong,but this time is works!!


    How do I get a summary of all the data based on a column from the master sheet or
    by the worksheet tabs?

  11. #11
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

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

    Can you tell me which cell's info you need in the summary?

  12. #12
    Registered User
    Join Date
    06-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    15

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

    So on a separate worksheet , The data would be summarized.

    For example column N contains different countries. The summary should have a count for all records that contain the same country.

    Sort by Column "N" Country field
    Then drill down using
    Column "L"contains "Y" or "N"

    Attaching a picture of my current outcome when manually entering the data.

    Attachment 270986

  13. #13
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

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

    Your attachment didnt load properly. Can you please try again?

  14. #14
    Registered User
    Join Date
    06-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    15

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

    Quote Originally Posted by arlu1201 View Post
    Your attachment didnt load properly. Can you please try again?
    I'm getting a strange error when trying to upload. Is there a size restriction of the files?
    Attached Files Attached Files

  15. #15
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

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

    How big is your file? There is a max limit of 1mb for excel files.

  16. #16
    Registered User
    Join Date
    06-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    15

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

    Im under the max. Were you able to get the doc2.docx file I uploaded? That file contains the screen shot I initially tried uploading

  17. #17
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

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

    I am not talking about a screenshot. You need to upload an excel file so we can work on it and give you a working solution.

  18. #18
    Registered User
    Join Date
    06-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    15

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

    Attachment "Week2" has the raw data I use to create attachment "progress" Is there a way to run a macro that will create "progress"?
    Attached Files Attached Files

  19. #19
    Registered User
    Join Date
    06-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    15

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

    Hi guys just curious if you had any luck? I have uploaded the file. Thanks

  20. #20
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

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

    This is your first post.
    I would like to take the contents of a column and split it into multiple worksheets.

    example Column b has values of Pa,GT,CR

    I would like to automatically create 3 tabs(worksheets) PA,GT,CR.
    But that doesnt match with the progress sheet you uploaded.

  21. #21
    Registered User
    Join Date
    06-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    15

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

    Ok . Ill open a new post

  22. #22
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

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

    What about this thread?

  23. #23
    Registered User
    Join Date
    06-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    15

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

    My intent was to eliminate some of the manual work it takes to compile progress. After receiving some excellent advice I was hoping to \automate other steps I manually do.

    IF this could be covered in one post it would be great, but if I need to open a new one please let me know.

  24. #24
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

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

    Ok, do you have anything pending to be solved as per your 1st post of this thread? If there is, we can work on it and then any additional stuff you want to be done, can be done in another thread.

  25. #25
    Registered User
    Join Date
    06-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    15

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

    Nothing is pending from my first thread. You can close this issue as solved. Thank you.

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [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
  2. Split worksheet content into multiple worksheets
    By macaco20 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-14-2012, 01:49 AM
  3. Split worksheet content into multiple worksheets
    By Bell8oy in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-24-2012, 08:41 AM
  4. Separating a Worksheet into multiple worksheets based on column A field.
    By Drifter33 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-27-2010, 04:16 PM
  5. Replies: 1
    Last Post: 05-02-2007, 10:36 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