PDA

View Full Version : Solved: PrintArea and Pagebreak woes



Glaswegian
01-31-2012, 07:45 AM
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
01-31-2012, 07:57 AM
Just a cursotry look Iain, but is it



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

Glaswegian
01-31-2012, 08:07 AM
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
01-31-2012, 08:08 AM
Can you post the workbook for me to try out?

Glaswegian
01-31-2012, 08:39 AM
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
01-31-2012, 08:51 AM
Not too sure but maybe page orientation could be the issue.
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

Glaswegian
01-31-2012, 09:01 AM
Thanks Shrivallabha - I did set up the Export sheet as Portrait - changing it to landscape would prove too much for some users!

shrivallabha
01-31-2012, 10:23 AM
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):
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

Glaswegian
02-01-2012, 05:54 AM
Thanks again - I decided just to set the margins via code instead - seemed easier and works just fine.

Thanks for your help.