Sleeper: Problem setting the Print Area

[lidane]

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]

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]

thanks mdmackillop 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]

You could always delete rows 101 down after copying.