View Full Version : Include Multiple column groups on every page?

05-06-2010, 11:27 AM

I have an Excel template with data that is creating individual worksheets using some VB code. The template contains a section of code that specifies the formatting for each newly created worksheet (landscape, legal paper, page breaks, etc).

Is there a way that I can use VBA to modify the Page Setup to include columns A, B, H and I on every page.

Such that Page 1 [Columns A-N], Page 2 [A,B,H,I,O-X], Page 3 [A,B,H,I,Y-AI] and so on.

Hopefully someone can point me in the right direction.


05-06-2010, 12:11 PM
Can you post your code?

05-06-2010, 12:20 PM
It's a pretty massive amount of code overall. In the setup for any new worksheet, here's how I'm defining the Page Setup properties...

newSht.PageSetup.PaperSize = xlPaperLegal
newSht.PageSetup.Orientation = xlLandscape
newSht.PageSetup.Order = xlOverThenDown
newSht.PageSetup.PrintTitleRows = "$1:$6"
newSht.PageSetup.PrintTitleColumns = "$A:$B"
newSht.VPageBreaks.Add Range("O1")
newSht.VPageBreaks.Add Range("Y1")
newSht.VPageBreaks.Add Range("AJ1")
newSht.VPageBreaks.Add Range("AV1")

Once the page is setup the rest is just a data dump.

I was trying before to include more than one section of columns, but that doesn't work...

newSht.PageSetup.PrintTitleColumns = "$A:$B"
newSht.PageSetup.PrintTitleColumns = "$H:$I"

05-06-2010, 02:26 PM
It appears PrintTitleColumns must be contiguous. Why not copy/insert for printing, then delete when finished?

05-06-2010, 02:29 PM
Also, try tidying up using With statements

With newsht
With .PageSetup
.PaperSize = xlPaperLegal
.Orientation = xlLandscape
.Order = xlOverThenDown
.PrintTitleRows = "$1:$6"
.PrintTitleColumns = Range(Columns("$A:$B"), Columns("$H:$I")).Address
End With
With .VPageBreaks
.Add Range("O1")
.Add Range("Y1")
.Add Range("AJ1")
.Add Range("AV1")
End With
End With