Excel - How to Include Multiple columns on every page

[cosmogramma]

Hello

I'm generating some excel spreadsheets using VBA to specify page setup. Is there a way that I can include columns A, B, H and I on every page.

Example:
Page 1 [Columns A-N]
Page 2 [A,B,H,I,O-X]
Page 3 [A,B,H,I,Y-AI]
Page 4 [A,B,H,I, etc]

I'm really hoping someone can point me in the right direction. I need to generate these tomorrow.

Thanks


Local Time: 01:27 PM
Local Date: 05-06-2010

 

[cosmogramma]

Not exactly. These spreadsheets are about 4 pages accross, and an undetermined number of rows. So page 5, page 9 and page 13, etc, need to behave like Page 1. (I am printing ACCROSS and DOWN.)

Every time the leftmost page is printed those columns need to be exposed. For the other 3 pages accross they need to be hidden.

Because the printing is dependant on my formatting changes, I need to be able to determine when I've reached the max number of rows that need to be printed.

 

[Gavona]

What is the last column on page 1, 5, 9?
What is the last column on page 4? Is this always so?

Does it matter if you don't print the pages in order? If not then print pages 1,5,9 etc first, then the remainder.


Gavin

 

[SkipVought]

HA! Today its 1, 5 & 9.

But NEXT WEEK, it may be 1, 5, 9, 13 & 17.

Congratulations. You have a full time job!

If I had a week to spare, I'd let you hire me. And I'm not sure I could find a suitable solution.

But maybe there's some hot shot who could--that's a definite maybe.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cosmogramma]

It does matter if I print them in order. If a user has a 30 page spreadsheet to print out, they need those pages appear in the correct order. I believe I've solved the problem of getting everything to print properly, I just can't get it to stop in any nice way.

I have an if statement within a loop that uses a Mod function to determine if I'm on the leftmost page and adjusts the columns accordingly.

For i = 0 To (rowCount - 1)
If (i Mod 4) = 0 Then
-expose columns, print page
Else
-hide columns, print page
Next i

My problem is that while this For..Next loop works, it's definitely going to run well beyond the actul printed region (number of rows in spreadsheet > number of pages).

Any suggestions for a different loop, or coding that would finish when there were no more pages to print?

 

[Gavona]

I think this approach, that simply temporarily replicates columns H&I so you can use the standard print functionality will do the job

Sub Macro1()
    Columns("H:I").Copy
    Columns("O:O").Insert Shift:=xlToRight
    Columns("H:I").Copy
    Columns("AA:AA").Insert Shift:=xlToRight
    Columns("H:I").Copy
    Columns("AN:AN").Insert Shift:=xlToRight

    With ActiveSheet.PageSetup
        .PrintArea = ""
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = "$A:$B"
        .Order = xlOverThenDown
    End With
    
    ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=Range("O1")
    ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=Range("AA1")
    ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=Range("AN1")

    ActiveWindow.SelectedSheets.PrintOut

    Range("AN:AO").Delete Shift:=xlToLeft
    Range("AA:AB").Delete Shift:=xlToLeft
    Range("O:P").Delete Shift:=xlToLeft

End Sub

Gavin

 

[cosmogramma]

Hey everyone, I resolved my printing problem. Turns out I don't have to make it my fulltime job to print this because there's a great function in VBA called HPagebreaks.

Since I know my spreadsheet will always be 4 Pages accross and ?? pages down, I can use a function like this to determine how long my For..Next loop has to go for.

maxPages = (ActiveSheet.HPageBreaks.Count + 1) * 4
For i = 0 To (maxPages - 1)
If (i Mod 4) = 0 Then
-expose columns, print page
Else
-hide columns, print page
Next i


I know this is a very specific situation, and probably won't be usefull to too many people, but figured I'd share what I was able to come up with.

 

[SkipVought]

These are HPageBreak Objects. As such, there is an HPageBreaks Collection of HPageBreak objects. This may be helpful to know.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}