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
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