Problems with Dynamic Page Setup/Page Breaks

[Achelray]

Hi,

Using Windows XP and Excel 2003. Trying to create dynamic code for three print scenarios (from all columns visible to a few columns hidden to the most columns hidden.) All columns visible (EXPANDED) is working, prints what I want on each legal size page; having problem with other two (SEMI COLLAPSED and COLLAPSED) Keep seeing the same page breaks for expanded. I have 12 months worth of data with 14 colums in each month and 24 "header" columns to the far left. EXPANDED shows header columns and 1 month of data on each legal page. SEMI COLLPASED shows 15 header columns and 14 columns for each month, but I want the 15 header columns and 2 months of data on each legal page. COLLAPSED shows 15 header columns and 6 columns for each month, but I want the 15 header columns and 3 months of data on each legal page. Below is the code I have for EXPANDED, SEMICOLLAPSED and COLLAPSED, what am I doing wrong?

Sub EXPANDEDPRINT()
Set wbBuyGuide = ActiveWorkbook
Set wsBUFBG = wbBuyGuide.Worksheets("BUYER UP FRONT BUY GUIDE")
Application.ScreenUpdating = False

lrBUFBG = wsBUFBG.Range("C65536").End(xlUp).Row
wsBUFBG.ResetAllPageBreaks
'wsBUFBG.PageSetup.PrintArea = "C1:GL" & lrBUFBG & ""
With wsBUFBG.PageSetup
.PrintArea = "C1:GL" & lrBUFBG & ""
.PrintTitleRows = "$1:$10"
.PrintTitleColumns = "$C:$Z"
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&Z&F"
.RightFooter = "Page &P"
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 42
'.FitToPagesWide = 12
'.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
End With
With wsBUFBG

.VPageBreaks.Add Before:=.Range("AO1")
.VPageBreaks.Add Before:=.Range("BC1")
.VPageBreaks.Add Before:=.Range("BQ1")
.VPageBreaks.Add Before:=.Range("CE1")
.VPageBreaks.Add Before:=.Range("CS1")
.VPageBreaks.Add Before:=.Range("DG1")
.VPageBreaks.Add Before:=.Range("DU1")
.VPageBreaks.Add Before:=.Range("EI1")
.VPageBreaks.Add Before:=.Range("EW1")
.VPageBreaks.Add Before:=.Range("FK1")
.VPageBreaks.Add Before:=.Range("FY1")
.Range("H1").ClearContents
.Range("H1").Select
End With

End Sub


Sub SEMICOLLAPSEDPRINT()
Dim rngMyRange As Range, rngCell As Range
Set wbBuyGuide = ActiveWorkbook
Set wsBUFBG = wbBuyGuide.Worksheets("BUYER UP FRONT BUY GUIDE")
Application.ScreenUpdating = False
lrBUFBG = wsBUFBG.Range("C65536").End(xlUp).Row
With wsBUFBG
wsBUFBG.ResetAllPageBreaks

End With

'wsBUFBG.PageSetup.PrintArea = "C1:GL" & lrBUFBG & ""
With wsBUFBG.PageSetup
.PrintArea = "C1:GL" & lrBUFBG & ""
.PrintTitleRows = "$1:$10"
.PrintTitleColumns = "$C:$Z"
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&Z&F"
.RightFooter = "Page &P"
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 44
'.FitToPagesWide = 3
'.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
End With

With wsBUFBG


.VPageBreaks.Add Before:=.Range("BQ1")
.VPageBreaks.Add Before:=.Range("DG1")
.VPageBreaks.Add Before:=.Range("EW1")


.Range("H1").ClearContents
.Range("H1").Select

End With

End Sub

Sub COLLAPSEDPRINT()
Dim rngMyRange As Range, rngCell As Range
Set wbBuyGuide = ActiveWorkbook
Set wsBUFBG = wbBuyGuide.Worksheets("BUYER UP FRONT BUY GUIDE")
Application.ScreenUpdating = False
lrBUFBG = wsBUFBG.Range("C65536").End(xlUp).Row
With wsBUFBG
wsBUFBG.ResetAllPageBreaks

End With

'wsBUFBG.PageSetup.PrintArea = "C1:GL" & lrBUFBG & ""
With wsBUFBG.PageSetup
.PrintArea = "C1:GL" & lrBUFBG & ""
.PrintTitleRows = "$1:$10"
.PrintTitleColumns = "$C:$Z"
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&Z&F"
.RightFooter = "Page &P"
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 55
'.FitToPagesWide = 3
'.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
End With

With wsBUFBG

.VPageBreaks.Add Before:=.Range("BC1")
.VPageBreaks.Add Before:=.Range("CE1")
.VPageBreaks.Add Before:=.Range("DG1")
.VPageBreaks.Add Before:=.Range("EI1")
.VPageBreaks.Add Before:=.Range("FK1")


.Range("H1").ClearContents
.Range("H1").Select

End With

End Sub