Michael Moeller has brought this to us :
Hi,
I have to print the columns of a worksheet, each one on a new page,
while columns A and B acting as headers, i.e.:
A B C
--- new page---
A B D
etc.
Can this be programmed in Excel?
tia
Michael
How many columns are you talking about?
I have an app that hides/unhides rows according to user selections in a
list. This is a pricing app that lists all possible items available as
options/accessories for a product. The list is hundreds of rows long but
the procedure only prices/prints what rows have a value in the QTY
column. The same can be done with columns where you could define the
entire print area and loop for each printout. This would, for each
iteration, hide all columns except A/B and unhide each of the others in
turn. So then...
1st pass: columns(1,2,3)
2nd pass: columns(1,2,4)
3rd pass: columns(1,2,5)
...and so on
The number of passes (UBound value for the loop) would be total cols
minus 1. So then...
Sub PrintMyCols()
Dim PrintRange As Range, RepeatRange As Range
Dim i As Long, lRows As Long, lMin As Long
lRows = ActiveSheet.UsedRange.Rows.Count
Set RepeatRange = Range("A1:B1")
Set PrintRange = RepeatRange.Resize(lRows, 5)
lMin = PrintRange.Columns.Count - RepeatRange.Columns.Count
For i = lMin To PrintRange.Columns.Count
Setup_ColsToPrint PrintRange, RepeatRange, i
ActiveSheet.PrintOut
Next
End Sub
[Possible concept for hiding columns:]
I use a cell formula to set a flag in a specific column that puts "P" in
any row that has a value entered in that row's QTY column (conditional
on the "Amount" column not having a calc error) In my usage it's easy
because AutoFilter works with columns, hiding rows that don't contain
"P" in the filtered column.
In your case I would have the dedicated procedure hide all columns in
the print range, and pass to it which columns to unhide.
'so..
Sub Setup_ColsToPrint(RangeToPrint As Range, _
RangeToRepeat As Range, _
ColToShow As Long)
RangeToPrint.EntireColumn.Hidden = True 'hide all
RangeToRepeat.EntireColumn.Hidden = False 'show repeats
'column to show this time
RangeToPrint.Columns(ColToShow).EntireColumn.Hidden = False
End Sub
HTH