PDA

View Full Version : Solved: Problem With .pagesetup.printarea



zoom38
08-27-2009, 08:42 AM
Hello all, below is a sub that I am using to print select months of my worksheet. The problem I am having is that should it be a Leap year, the print range will change. So in my code I tried to accomodate for it but I can't get it to work. I get an error on the line below in the [Case Is = "MAR", "MARCH"].

ActiveSheet.PageSetup.PrintArea = myPrintRange

Can someone steer me in the right direction.

Thanks
Gary


Sub SelectMonth()

Dim year As Integer
Dim myPrintRange As Range
year = Right(Range("A1"), 4)

PrintMonth = InputBox(prompt:="What Month Do You Wish To Print?", Title:="Choose A Month To Print", Default:="Jan,Feb,Mar,Apr,May,Jun,Jul,Sep,Oct,Nov,Dec")

PrintMonth = UCase(PrintMonth)

Select Case Trim(PrintMonth)
Case Is = "JAN", "JANUARY"
Range("A1:T38").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$T$38"

Case Is = "FEB", "FEBRUARY"
If IsLeapYear(year) = False Then
Range("A40:T74").Select
Else: Range("A41:T75").Select
End If
ActiveSheet.PageSetup.PrintArea = "$A$40:$T$74"

Case Is = "MAR", "MARCH"
If IsLeapYear(year) = False Then
Set myPrintRange = Range("A76:T113") '.Select
Else: myPrintRange = Range("A77:T114") '.Select
End If
myPrintRange.Select

ActiveSheet.PageSetup.PrintArea = myPrintRange

Case Else
End
End Select
End Sub

tpoynton
08-27-2009, 09:59 AM
Case Is = "MAR", "MARCH"
If IsLeapYear(year) = False Then
Set myPrintRange = Range("A76:T113") '.Select
Else: set myPrintRange = Range("A77:T114") '.Select
End If
myPrintRange.Select

ActiveSheet.PageSetup.PrintArea = myPrintRange
i think you just need to 'set' the myprintrange

zoom38
08-27-2009, 10:06 AM
Thanks for the reply but that has no effect. I did set the range on the first line which is the one that is used since it is not a leap year. I still get the error message on this line:

ActiveSheet.PageSetup.PrintArea = myPrintRange

tpoynton
08-27-2009, 10:12 AM
can you post a sample workbook? I modified below, and get no errors for march
Sub SelectMonth()

Dim year As Integer
Dim myPrintRange As Range
Dim printmonth As String
Dim isleapyear As Boolean
year = 2001
isleapyear = False
printmonth = InputBox(prompt:="What Month Do You Wish To Print?", Title:="Choose A Month To Print", Default:="Jan,Feb,Mar,Apr,May,Jun,Jul,Sep,Oct,Nov,Dec")

printmonth = UCase(printmonth)

Select Case Trim(printmonth)
Case Is = "JAN", "JANUARY"
Range("A1:T38").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$T$38"

Case Is = "FEB", "FEBRUARY"
If isleapyear = False Then
Range("A40:T74").Select
Else: Range("A41:T75").Select
End If
ActiveSheet.PageSetup.PrintArea = "$A$40:$T$74"

Case Is = "MAR", "MARC"
If isleapyear = False Then
Set myPrintRange = Range("A76:T113") '.Select
Else: Set myPrintRange = Range("A77:T114") '.Select
End If
myPrintRange.Select

ActiveSheet.PageSetup.PrintArea = myPrintRange

Case Else
End
End Select
End Sub

zoom38
08-27-2009, 10:30 AM
Module 3

tpoynton
08-27-2009, 10:39 AM
try ActiveSheet.PageSetup.PrintArea = myPrintRange.Address

zoom38
08-27-2009, 10:58 AM
That worked, thank you very much.
Gary