+ Reply to Thread
Results 1 to 6 of 6

How to Split Excel Spreadsheets into new .xls files based on their cell names?

  1. #1
    Registered User
    Join Date
    08-13-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    88

    How to Split Excel Spreadsheets into new .xls files based on their cell names?

    Hi,

    I need to split a large worksheet up into multiple worksheets. What I need done:

    1) A macro to split all spreadsheets and save them as .xls files with the name of their corresponding tab.
    2) For formatting not to be lost e.g. merged cells. Cell functions etc can be lost.

    Any help would be greatly appreciated.

    I tried using this code below but it had a large amount of debug issues and lost the formatting of the cells.

    Please Login or Register  to view this content.

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: How to Split Excel Spreadsheets into new .xls files based on their cell names?

    Hi Ardiko,

    I changed your code just a little (trimmed it a bit) - see if it works for you:

    Please Login or Register  to view this content.
    BTW - you should get used to using the Option Explicit and declaring your variables - it will save you headaches
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    08-13-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    88

    Re: How to Split Excel Spreadsheets into new .xls files based on their cell names?

    Thanks xladept! I'll try this out now.

    You know of any good videos on Option Explicit and declaring your variables? Never did any coding in my life =\

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: How to Split Excel Spreadsheets into new .xls files based on their cell names?

    Check out the tips and tutorials forum. And, I understand that youtube has excel tutorials. But, you should have a reference book for VBA coding. (I learned with Steve Romans "Writing Excel Macros" by O'Reilly and many have John Walkenbach's "Excel 20xx Power Programming" - I got a copy from Amazon for about $25)

    BTW - Let me know if the formats don't transfer

  5. #5
    Registered User
    Join Date
    08-13-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    88

    Re: How to Split Excel Spreadsheets into new .xls files based on their cell names?

    Cheers! I'll check it out! The vba code works perfectly

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: How to Split Excel Spreadsheets into new .xls files based on their cell names?

    Fantastic!

+ 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. split text files stored in some location to multiple files based on a condition
    By GIRISH_KH in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-01-2013, 11:32 AM
  2. Split Excel into Multiple csv files based upon the sum of column count.
    By sukanya123 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-02-2013, 07:28 PM
  3. Split Excel file into multiple files based on number of rows
    By kingtut86 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-24-2013, 03:38 PM
  4. [SOLVED] Split Workbook into Multiple Files based on Worksheet Name + Cell Value
    By CRW in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-11-2012, 07:05 AM
  5. Replies: 3
    Last Post: 08-02-2006, 12:35 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