+ Reply to Thread
Results 1 to 7 of 7

[Solved] Split into multiple sheets by page breaks, preserving formatting

  1. #1
    Registered User
    Join Date
    11-15-2010
    Location
    Arlington, VA
    MS-Off Ver
    Excel 365
    Posts
    34

    [Solved] Split into multiple sheets by page breaks, preserving formatting

    Hi,

    I am employing the solution at http://www.rondebruin.nl/win/s3/win005.htm in a worksheet to split the data of one massive worksheet into several, based on page breaks.

    However, the row height and column widths are not being preserved. I have figured a simple enough way to take care of column widths, but the row heights are tough because they don't follow a pattern.

    Is there any way to modify this routine to preserve the column widths and row heights when making the paste operation into the new sheets?

    Bonus points: Instead of doing this by page break, how could I do this via every instance of a phrase? Every page in the file has the same title, so maybe I can use that instead of creating page breaks in the first place.
    Last edited by tecsbrain; 10-07-2014 at 03:26 PM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Split into multiple sheets by page breaks, preserving formatting

    To attach a Workbook
    (please do not post pictures or links to worksheets)
    • Click Advanced (next to quick post),
    • Scroll down until you see "Manage Attachments",
    • Click that then select "add files" (top right corner).
    • Click "Select Files" find your file, click "open" click "upload"
    • Once the upload is completed the file name will appear below the input boxes in this window.
    • Click "Done" at bottom right to close the Attachment Manager.
    • Click "Submit Reply"
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    11-15-2010
    Location
    Arlington, VA
    MS-Off Ver
    Excel 365
    Posts
    34

    Re: Split into multiple sheets by page breaks, preserving formatting

    Deleting my original reply -- I just decided to pay attention to the color of your text and noticed that you emphasized not to link to worksheets.

    I wasn't linking to the worksheet, just to a macro that I wanted to use. Apologies if that's against SOP, here's the code:

    Please Login or Register  to view this content.
    Thanks!
    Attached Files Attached Files
    Last edited by tecsbrain; 10-03-2014 at 01:46 PM.

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Split into multiple sheets by page breaks, preserving formatting

    Try this one (tested):
    Based on searching for header A1:C1 of first page

    Please Login or Register  to view this content.
    Last edited by protonLeah; 10-04-2014 at 02:44 PM.

  5. #5
    Registered User
    Join Date
    11-15-2010
    Location
    Arlington, VA
    MS-Off Ver
    Excel 365
    Posts
    34

    Re: Split into multiple sheets by page breaks, preserving formatting

    This one gives me a "Compile Error: Syntax Error" with line 21 "With" highlighted. Any ideas? Tried adding it as a module and an object.

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Split into multiple sheets by page breaks, preserving formatting

    Sorry, correction in RED below
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    11-15-2010
    Location
    Arlington, VA
    MS-Off Ver
    Excel 365
    Posts
    34

    Re: Split into multiple sheets by page breaks, preserving formatting

    Worked a treat, cheers and thanks

+ 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] Split Cell containing multiple soft breaks
    By Bernaar in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-09-2013, 03:13 PM
  2. Split Excel Spreadsheet Data Into New Workbooks based on page breaks
    By sarndt01 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-23-2013, 02:14 AM
  3. Saving to PDF with page breaks so charts aren't split
    By wizgf19 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-23-2012, 05:21 PM
  4. Replies: 1
    Last Post: 03-15-2009, 06:08 PM
  5. [SOLVED] formatting page breaks
    By Anze in forum Excel General
    Replies: 3
    Last Post: 04-23-2005, 12:06 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