Closed Thread
Results 1 to 13 of 13

Split worksheet content into multiple worksheets

  1. 01-12-2010, 08:11 PM #1
    Bell8oy
    Bell8oy is offline
    Registered User
    Join Date
    01-12-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    3

    Split worksheet content into multiple worksheets

    Hello

    Thank you for taking the time to help me with this problem.

    My excel requirements have been VERY basic until now. (never created a macro / sum etc.)

    I've spent many hours 'google-ing' my problem without success. (I know my solution is out there )

    Using a macro, I need to split a worksheet based on the content of column A.

    i.e.

    Site 003
    Site 004

    Creating a new worksheet of the same name. Please see attached document.

    I have found some VB code to use.... but am stuck (sorry my formatting probably stinks!)
    Please Login or Register  to view this content.
    Please advise if possible... and possibly point me in the right direction of some documentation that may help.

    Let me know if you need more info... or if I'm not making sense.

    Thank you!
    Chris
    Attached Files Attached Files
    Last edited by Paul; 01-12-2010 at 10:20 PM. Reason: Added code tags for new user. Please read the forum rules.
    Reply With QuoteRegister To Reply
  2. 01-12-2010, 08:38 PM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Split worksheet content into multiple worksheets

    You'll need to edit your post above and add code tags to the code, like I did below.
    ==========

    1) Remove the macro you had in the Sheet module, it doesn't really go there.

    2) Click INSERT > MODULE to add a blank standard module and paste in this macro:
    Please Login or Register  to view this content.
    Save your sheet. Give it a try.
    Last edited by JBeaucaire; 01-12-2010 at 08:43 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 01-12-2010, 08:39 PM #3
    rylo
    rylo is offline
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Split worksheet content into multiple worksheets

    Hi

    This is based on the data in your example file. Put the code below into a general module and see how it goes.

    Please Login or Register  to view this content.
    Also, can you please make sure you wrap any code in the code tags in future posts.

    rylo
    Reply With QuoteRegister To Reply
  4. 01-13-2010, 06:52 AM #4
    Bell8oy
    Bell8oy is offline
    Registered User
    Join Date
    01-12-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Split worksheet content into multiple worksheets

    Thanks everyone for your help. I will try these examples now.

    (Thanks Paul for adding code tags to my original post :-) - I will make sure I do this in future)
    Reply With QuoteRegister To Reply
  5. 01-13-2010, 07:44 AM #5
    Bell8oy
    Bell8oy is offline
    Registered User
    Join Date
    01-12-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Split worksheet content into multiple worksheets

    Thank you JBeaucaire, your module is perfect. I am able refresh the content of the 'Data' worksheet and split out into multiple worksheets based on site.

    Thank you rylo, your module also works great. I like the part where is adds columns headers so I will try and use parts of both modules to get what I need.

    Thanks again everyone,
    Chris
    Reply With QuoteRegister To Reply
  6. 01-13-2010, 12:40 PM #6
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Split worksheet content into multiple worksheets

    If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


    (Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)
    Reply With QuoteRegister To Reply
  7. 08-23-2011, 06:40 PM #7
    Ashish Vijayvargiya
    Ashish Vijayvargiya is offline
    Registered User
    Join Date
    08-23-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2007
    Posts
    1

    Smile Re: Split worksheet content into multiple worksheets

    Thanks JB for your help, I am working on similar request and your code is working great, however, out of my curiosity I would like to understand the action of the below codes of your procedure.
    ----------------------------------------------------------------------------------------------------------------------------------

    Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("CC1"), Unique:=True
    Columns("CC:CC").Sort Key1:=Range("CC2"), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    MyArr = Application.WorksheetFunction.Transpose(Range("CC2:CC" & Rows.Count).SpecialCells(xlCellTypeConstants))

    If Not Evaluate("=ISREF('" & MyArr(i) & "'!A1)") Then
    ----------------------------------------------------------------------------------------------------------------------------------

    I look foward for your help, Thanks
    Ashish VV
    Reply With QuoteRegister To Reply
  8. 08-23-2011, 06:58 PM #8
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Split worksheet content into multiple worksheets

    You can read up on the Advanced Filter in Excel's help system by pressing F1. 


    Please Login or Register  to view this content.
    In this line of code I used the Advanced Filter to extract a "unique" list of all the values in column A, and dropped that list into column CC temporarily.
    Please Login or Register  to view this content.
    Next we sorted the unique list to put them in alphabetical order.

    Please Login or Register  to view this content.
    Now, we put all those unique ordered values into an array in memory called MyArr.

    Please Login or Register  to view this content.
    We construct an ISREF() formula using a value from the array and attaching it to cell A1, then "evaluate" that formula to see if it's resolves to a real cell reference in our workbook. If it does NOT, that means the sheet doesn't exist, so we create it. If the sheet did exist, we move the existing sheet to the end of the workbook. In both instances the named sheet ends up in the same place, at the end of the workbook.

    As that code loops through all the values, it creates/moves sheets resulting in the sheets being in alphabetical order, too.
    ----------------------------------------------------------------------------------------------------------------------------------

    I look foward for your help, Thanks
    Ashish VV[/QUOTE]
    Reply With QuoteRegister To Reply
  9. 12-16-2011, 08:16 AM #9
    gmikulski
    gmikulski is offline
    Registered User
    Join Date
    12-16-2011
    Location
    Poland
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Split worksheet content into multiple worksheets

    Hello

    I am trying to use JBeaucaire's macro in my file, but I encountered a problem.

    JB - can you help?

    I am using your macro to divide some data into sheets. My macro goes though a large list and generates separate files. Then on a generated file your macro is run.

    However, it gets stuck at some point: if there is only one element in newly created array (sometimes in new file in the given column there is only one and same entry, so the array has one element).

    Please Login or Register  to view this content.
    Ubound fails to work when there is only one item in array - I am getting Run-time error 13 Type Mismatch.

    As I am only starting with Arrays - would you be able to give me a hint how to get past this?

    Thanks a lot!
    Greg
    Reply With QuoteRegister To Reply
  10. 12-16-2011, 08:46 AM #10
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Split worksheet content into multiple worksheets

    Do you want to get past this? If there is only one value in the array (it's not an array actually), then wouldn't you just stop?

    You could add a check to make sure there are at least two values in the array before doing anything else.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  11. 12-16-2011, 08:54 AM #11
    gmikulski
    gmikulski is offline
    Registered User
    Join Date
    12-16-2011
    Location
    Poland
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Split worksheet content into multiple worksheets

    Yes, I need to get past this and macro should carry on.

    You see - I have a large database. My macro divides this database into separate files (based on column A where I have names of companies).

    Once the file for company A is created, it contains sheet TOTAL. I am using your macro to divide that sheet into separate sheets.

    In case there is only 1 sheet, I want the macro to continue (as I still have some other companies in main database).

    I will use your hint and try to work this around. Thanks.
    Reply With QuoteRegister To Reply
  12. 05-24-2012, 08:33 AM #12
    samuelnadar.2009
    samuelnadar.2009 is offline
    Registered User
    Join Date
    05-23-2012
    Location
    mumbai
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Split worksheet content into multiple worksheets

    Quote Originally Posted by JBeaucaire View Post
    You'll need to edit your post above and add code tags to the code, like I did below.
    ==========

    1) Remove the macro you had in the Sheet module, it doesn't really go there.

    2) Click INSERT > MODULE to add a blank standard module and paste in this macro:
    Please Login or Register  to view this content.
    Save your sheet. Give it a try.
    hi
    i tried this code with my file but i am unable to process..i am getting a message to debug..please help me out..
    i am attaching the file for your reference
    i want this type of file to be split as per specific coloumn sorted into specific folder i mention..
    please i need this badly
    my email id : samuelnadar.2009@gmail.com
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  13. 05-24-2012, 08:41 AM #13
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Split worksheet content into multiple worksheets

    Samuel,

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    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]
    Reply With QuoteRegister To Reply
Closed Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1