Solved: PrintArea and Pagebreak woes

[Glaswegian]

Folks

I seem to be having a severe blond moment and could do with some help.

As part of a larger routine, I'm exporting a 'results' sheet to a new workbook. Users will need to print the new sheet, so I set some column widths etc so that everything will appear on one single A4 sheet. The export works fine, but when I try to set a PrintArea or clear Pagebreaks neither works at all and the resultant print is spread over 2 pages.

The code for this part is

With newBook.ActiveSheet
    .Range("B1").Activate
    .Paste
    .Cells.PageBreak = xlPageBreakNone
    .Columns(1).ColumnWidth = 2.57
    .Columns(2).ColumnWidth = 1
    .Columns("C:N").ColumnWidth = 8.43
    .Columns("O").ColumnWidth = 2.43
    .PageSetup.PrintArea = .Range("B1", Cells(Columns.Count, 14)).End(xlUp)
End With

I know this is something silly but my brain is obviously not working today.

Thanks for puting me out of my misery.

[Bob Phillips]

Just a cursotry look Iain, but is it

[vba]

With newBook.ActiveSheet
.Range("B1").Activate
.Paste
.Cells.PageBreak = xlPageBreakNone
.Columns(1).ColumnWidth = 2.57
.Columns(2).ColumnWidth = 1
.Columns("C:N").ColumnWidth = 8.43
.Columns("O").ColumnWidth = 2.43
.PageSetup.PrintArea = .Range("B1", .Cells(.Columns.Count, 14)).End(xlUp)
End With[/vba]

[Glaswegian]

Hi xld

Thanks - but I'm afraid that makes no difference - although that was a good spot - told you I was having a bad day!

As soon as I click print, a pagebreak appears between cols K & L. The format and layout is designed (and was tested) to print out on one page - this is really frustrating!

Edit > meant to say that I've now moved on to XL2010.

[Bob Phillips]

Can you post the workbook for me to try out?

[Glaswegian]

Sorry I can't - too much confidential info - but here is the full routine - launched via any one of several Option Buttons.

The workbook is an automated decision tree. Users choose Yes or No Command Buttons and end up with a choice of Option Buttons.

Sub SaveDecisionToNewBook()
Dim fName, newBook
Dim myRng As Range
Dim c As Range
Dim x
Application.ScreenUpdating = False
'advise the user
MsgBox "The Decision will now be saved as a new workbook." & vbCr & vbCr & _
    "You must complete the document in the new file" & vbCr & vbCr & _
    "and save the new file to BLP." & vbCr & vbCr & _
    "You will be prompted for a File Name for the new workbook.", vbInformation + vbOKOnly, "Save File"
Set myRng = Sheets("Start").Range("Y2:Y90") 'the range where the final answer value is stored
For Each c In myRng                         'loop through the cells in the range
    If c.Value = "Y" Then                   'determine which Decision is required
        Select Case c.Offset(0, -1).Value   'check the column to the left
            Case Is = "Yes17": Set x = Sheets("Export").Range("ILAInc") 'Increase
            Case Is = "Yes23": Set x = Sheets("Export").Range("ILAMajVar") 'Major Variation
            Case Is = "Opt19", "Opt22", "Opt25", "Opt28", "Opt31", "Opt43", "Opt40", "Opt52", "Opt55", "Opt58", "Opt61", "Opt64", "Opt67": Set x = Sheets("Export").Range("_ILA1") 'ILA 1
            Case Is = "Opt20", "Opt23", "Opt26", "Opt29", "Opt32", "Opt44", "Opt41", "Opt50", "Opt53", "Opt56", "Opt59", "Opt62", "Opt65": Set x = Sheets("Export").Range("_ILA2") 'ILA 2
            Case Is = "Opt21", "Opt24", "Opt27", "Opt30", "Opt33", "Opt42", "Opt45", "Opt51", "Opt54", "Opt57", "Opt60", "Opt63", "Opt66": Set x = Sheets("Export").Range("ILADec") 'Declined
            Case Is = "Opt70", "Opt73", "Opt76", "Opt79", "Opt82", "Opt103", "Opt106": Set x = Sheets("Export").Range("_ILA1")  'ILA 1
            Case Is = "Opt68", "Opt71", "Opt74", "Opt77", "Opt80", "Opt83", "Opt86", "Opt89", "Opt92", "Opt95", "Opt98", "Opt104", "Opt107", "Opt110": Set x = Sheets("Export").Range("_ILA2") 'ILA 2
            Case Is = "Opt69", "Opt72", "Opt75", "Opt78", "Opt81", "Opt84", "Opt105", "Opt108": Set x = Sheets("Export").Range("ILA4") 'Declined
            Case Is = "Yes19": Set x = Sheets("Export").Range("ILAMinor") ' Minor Variation
            Case Is = "Yes24": Set x = Sheets("Export").Range("ILA9") ' Major Variation
            Case Is = "Yes212": Set x = Sheets("Export").Range("ILA3Party") 'Charge
                Case Else
                    MsgBox "No values present - please check input", vbOKOnly + vbInformation, "Cannot export - data missing"
                    Exit Sub
        End Select
    End If
Next c
 
x.Copy                                      'copy the required Decision
Set newBook = Workbooks.Add                 'create a new workbook
With newBook.ActiveSheet
    .Range("B1").Activate
    .Paste
    .Cells.PageBreak = xlPageBreakNone
    .Columns(1).ColumnWidth = 2.57
    .Columns(2).ColumnWidth = 1
    .Columns("C:N").ColumnWidth = 8.43
    .Columns("O").ColumnWidth = 2.43
    .PageSetup.PrintArea = .Range("B1", Cells(.Columns.Count, 14)).End(xlUp)
End With
ActiveWindow.DisplayGridlines = False
Application.CutCopyMode = False             'clear copy/paste
fName = Application.GetSaveAsFilename(fileFilter:="Excel Files (*.xls), *.xls")
    If fName <> False Then                  'get a file name from the user
        With newBook
            .SaveAs Filename:=fName         'save the new workbook using the file name provided
            .Close                          'close the new workbook
        End With
        MsgBox "The Decision has now been saved." & vbCr & vbCr & _
        "Remember to save the new file to BLP.", vbInformation + vbOKOnly, "Save File" 'advise the user
        MsgBox "You may now close and save this workbook." & vbCr & vbCr & _
        "This Decision Tree can be saved as a separate" & vbCr & vbCr & _
        "file for the next steps to be followed.", vbInformation + vbOKOnly, "Close Workbook"  'advise the user
        Else
            MsgBox "You must save the Decision - it must be attached to BLP.", vbInformation + vbOKOnly, "Save the file"
            newBook.Close False             'close the workbook
            Exit Sub
    End If
Application.ScreenUpdating = True
End Sub

Thanks again for your help.

[shrivallabha]

Not too sure but maybe page orientation could be the issue.
[VBA]With newBook.ActiveSheet
.Range("B1").Activate
.Paste
.Cells.PageBreak = xlPageBreakNone
.Columns(1).ColumnWidth = 2.57
.Columns(2).ColumnWidth = 1
.Columns("C:N").ColumnWidth = 8.43
.Columns("O").ColumnWidth = 2.43
.PageSetup.PrintArea = .Range("B1", .Cells(.Columns.Count, 14)).End(xlUp)
.PageSetup.Orientation = xlLandscape
End With[/VBA]

[Glaswegian]

Thanks Shrivallabha - I did set up the Export sheet as Portrait - changing it to landscape would prove too much for some users!

[shrivallabha]

When I tried your code (on Excel 2007), it gave me the split exactly between K and L (as you have mentioned in post #3). However, adding this made it to fit on one page (width only):
[VBA]With newBook.ActiveSheet
.Range("B1").Activate
.Paste
.Cells.PageBreak = xlPageBreakNone
.Columns(1).ColumnWidth = 2.57
.Columns(2).ColumnWidth = 1
.Columns("C:N").ColumnWidth = 8.43
.Columns("O").ColumnWidth = 2.43
.PageSetup.PrintArea = .Range("B1", .Cells(.Columns.Count, 14)).End(xlUp)
.PageSetup.FitToPagesWide = 1
End With[/VBA]

[Glaswegian]

Thanks again - I decided just to set the margins via code instead - seemed easier and works just fine.

Thanks for your help.