PDA

View Full Version : Sleeper: Macro to print area problem



malik641
09-16-2005, 10:05 AM
I'm trying to print out a specified area on a sheet using a defined name as the range. Here is my code:



Sub Print_AF004()
Application.ScreenUpdating = False
Dim oldCell As Range
Set oldCell = ActiveCell
Range("Freezer_AF004").Select
ActiveSheet.PageSetup.PrintArea = Range("Freezer_AF004")
With ActiveSheet.PageSetup
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlPortrait
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=True, Collate:=True
ActiveSheet.PageSetup.PrintArea = ""
oldCell.Select
End Sub

I set this to an object button (rectangle shape). When I run the macro it tells me that I have only one cell selected for print.

The reason for the printpreview is to check to see of the print area was set correctly, which it is not for some reason :dunno

And BTW, the "Freezer_AF004" is the defined name (you can make it any range you want, really).

Any ideas??
Thanks

Zack Barresse
09-16-2005, 10:09 AM
A couple of suggestions for you Joseph ...


Sub Print_AF004()
Application.ScreenUpdating = False
Dim oldCell As Range
'Set oldCell = ActiveCell
'You're better off using Selection, in case of multiple cell selections
Set oldCell = Selection
Range("Freezer_AF004").Select '<< Not sure why you need to select it first...
'You need the address, the PrintArea property is a String, not a Range
ActiveSheet.PageSetup.PrintArea = Range("Freezer_AF004").Address
With ActiveSheet.PageSetup
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlPortrait
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=True, Collate:=True
ActiveSheet.PageSetup.PrintArea = ""
oldCell.Select
'You must set this back to True, it's good habit (not all versions do this for you)
Application.ScreenUpdating = True
End Sub

malik641
09-16-2005, 11:19 AM
Thanks Zack :thumb
I was working off of a recorded macro. I knew there was a way to do all of that without selecting any cells :doh: .

I modified it a bit more:



Sub Print_AF004()
Application.ScreenUpdating = False
'Set Print Area
ActiveSheet.PageSetup.PrintArea = "Freezer_AF004"
'Set Page Setup
With ActiveSheet.PageSetup
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlPortrait
.FitToPagesWide = 1
.FitToPagesTall = 1
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0)
.BottomMargin = Application.InchesToPoints(0)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
End With
'Print
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=False, Collate:=True
'Clear Print Area
ActiveSheet.PageSetup.PrintArea = ""
Application.ScreenUpdating = True
End Sub

I'm pretty sure this will do just fine. Any other suggestions? Or does it look good to you guys??

malik641
09-16-2005, 11:30 AM
Actually, I do have another question. How to I make the macro so the print prompt will come up, rather than immediately print it???