PDA

View Full Version : Sleeper: Problem setting the Print Area



lidane
07-13-2005, 09:46 AM
Hi,

I have a macro to copy info into a new workbook. I want to set the print area of the active sheet in the new w/book to C3:Z60 and also to fit to 1 page when printing. Here's my code -



With ActiveSheet
.Paste
.Cells.Copy
.Cells.PasteSpecial xlValues
.Cells.RemoveSubtotal
.Cells.Validation.Delete
.Name = "SAVE"
.Cells.EntireColumn.AutoFit
.Range("A1,g1,l1,n1,p1,s1,v1").EntireColumn.ColumnWidth = 2
.Range("B1").EntireColumn.Hidden = True
.Range("C1").EntireColumn.ColumnWidth = 12
.Range("h1:K1,m1").EntireColumn.ColumnWidth = 10
.PageSetup.Orientation = xlLandscape
.PageSetup.Zoom = False
.PageSetup.PrintArea = "$c$3:$z$60"
.PageSetup.FitToPagesWide = 1
.PageSetup.FitToPagesTall = 1
End With


When I check the print preview it shows about 190 pages to print. Please can someone advise me what I am doing wrong?

Thanks!

mdmackillop
07-13-2005, 10:23 AM
Hi Lidane,
Your basic code works for me.
I tried a slightly different approach as well, to copy to the new workbook; see if it makes any difference.


Sub SaveToNew()
ActiveSheet.Copy
With ActiveSheet
.Cells.Copy
.Cells.PasteSpecial xlValues
.Cells.RemoveSubtotal
.Cells.Validation.Delete
.Name = "SAVE"
.Cells.EntireColumn.AutoFit
.Range("A1,g1,l1,n1,p1,s1,v1").EntireColumn.ColumnWidth = 2
.Range("B1").EntireColumn.Hidden = True
.Range("C1").EntireColumn.ColumnWidth = 12
.Range("h1:K1,m1").EntireColumn.ColumnWidth = 10
.PageSetup.Orientation = xlLandscape
.PageSetup.Zoom = False
.PageSetup.PrintArea = "$c$3:$z$60"
.PageSetup.FitToPagesWide = 1
.PageSetup.FitToPagesTall = 1
End With
ActiveWindow.SelectedSheets.PrintPreview
End Sub

lidane
07-13-2005, 10:54 AM
thanks mdmackillop (http://www.vbaexpress.com/forum/member.php?u=87) vbmenu_register("postmenu_34493", true); ,

that did work - unfortunately, I realise I didn't explain that well in my first post...I only want to copy the first 100 rows of the copy s/sheet...

so I need to use something like


activesheet.rows("1:100").copy
workbooks.add


instead of


activesheet.copy

when I do the first way it is still showing 190 pages in the print preview

mdmackillop
07-13-2005, 12:21 PM
You could always delete rows 101 down after copying.