Managing Page Breaks with VBA
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Managing Page Breaks with VBA

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    134
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I've created a Macro which runs through an old worksheet, copies most of the data and pastes into a new worksheet where it is then reformatted. The last thing I need to do is set the page breaks so that the worksheet is only one page wide (it can be an indefinite number of pages long). I have used the following code to achieve this:

    With ActiveSheet.PageSetup
    .AdjustTo = False
    .FitToPagesWide = 1
    .FitToPagesTall = False
    .FitTo = True
    End With

    This works at setting the Fit To section of the Page set up to 1 by blank but the radio button stays in the Adjust To part. It's not that the worksheet is just too wide for this to work since it is only three columns and when I manually set it to one page by blank it works fine. For some reason it just insists on leaving the page break after the second column. I've even tried the following code which doesn't seem to have any effect at all on the worksheet:

    ActiveSheet.VPageBreaks(1)DragOff Direction:=xlToRight, RegionIndex:=1

    ActiveSheet.VPageBreaks(1).Location = ActiveSheet.Range("c5")

    All of this is simply an attempt to make one page wide which includes only columns A, B, and C.

    Any ideas?

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try the following code:
    With ActiveSheet.PageSetup
    .PrintArea = "$A:$C"
    .FitToPagesWide = 1
    .FitToPagesTall = False
    End With

    The code sets the print area to columns A:C and set the page wide to 1.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    Holland
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Does it help to ADD to your code for the
    pagesetup:

    .PageSetup.Zoom=False

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    134
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thanks guys! Adding both the Print Area and setting the Zoom to False finally set my page breaks right.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com