Printing problem
 

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

Printing problem
 

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.Hidde n = False
End Sub

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Printing problem
 

GS expressed precisely :
The number of passes (UBound value for the loop) would be total cols
minus 1.

Oops! This line should read..

The number of passes (UBound value for the loop) would be total cols
minus 2.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Printing problem
 

GS wrote:
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.Hidde n = False
End Sub

HTH

Garry,

thank you. I understand what the program does in principal.
Unfortunately I'm working with Unix and I don't know much VB. I fiddled
around with the code 'til the VB compiler was happy and no runtime
errors occured but my printer either got stuck after a few rows of A and
B, or printed out A and B alone. Obviously I do something wrong.
Do I have to declare the ranges in the code or is it taken from the
marked area?
It would be of great help if you could give me a working example of,
say, A and B fixed and N cols printed with M rows.

regards
Michael

Printing problem
 

Michael,

This code was created in Excel's VBA (Visual Basic for Applications)
editor (VBE). If you're running MS Office on a Unix machine then I
suspect it can be accessed via Alt+F11. VBA is the macro language used
by all MSO apps.

The code sample IS a working example 'as is'! You may need to define
your Print_Area to be the same as its RangeToPrint, as well as do
whatever page setup desired so your printer receives the right
instruction for your desired results.

All you need to do is have the sheet to be printed as the active sheet
(the one you're currently working on) and run the macro named
'PrintMyCols'. The 'Setup_ColsToPrint' macro is a helper procedure that
handles column visibility only before each printout.

If you need VBA samples for other things you'd like to do you can use
the macro recorder and go through it manually once, stop the recorder,
and view the generated code in the VBE.

I'v inserted comments to further document what the code below does...

Sub PrintMyCols()
'//Declare variables & type
Dim PrintRange As Range, RepeatRange As Range
Dim i As Long, lRows As Long, lCols As Long, lMin As Long

'//Get the total number of rows/cols to print
lRows = ActiveSheet.UsedRange.Rows.Count
lCols = ActiveSheet.UsedRange.Columns.Count

'//Get a reference to the cols to repeat on each printout
Set RepeatRange = Range("A1:B1")
'//Get the size of the area to print
Set PrintRange = RepeatRange.Resize(lRows, lCols)

'Get the first column to start with
lMin = PrintRange.Columns.Count - RepeatRange.Columns.Count

'Loop thru each column of the area to be printed
For i = lMin To PrintRange.Columns.Count
'//Hide-Unhide cols according to loop index (i)
Setup_ColsToPrint PrintRange, RepeatRange, i
'//Print the sheet
ActiveSheet.PrintOut
'//Setup the next print job
Next
End Sub

Sub Setup_ColsToPrint(RangeToPrint As Range, _
RangeToRepeat As Range, _
ColToShow As Long)
RangeToPrint.EntireColumn.Hidden = True 'hide all
RangeToRepeat.EntireColumn.Hidden = False 'show repeats
'set which column to print this time
RangeToPrint.Columns(ColToShow).EntireColumn.Hidde n = False
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Printing problem
 

GS wrote:
Michael,

This code was created in Excel's VBA (Visual Basic for Applications)
editor (VBE). If you're running MS Office on a Unix machine then I
suspect it can be accessed via Alt+F11. VBA is the macro language used
by all MSO apps.

The code sample IS a working example 'as is'! You may need to define
your Print_Area to be the same as its RangeToPrint, as well as do
whatever page setup desired so your printer receives the right
instruction for your desired results.
....
----------------

Garry,

I greately appreciate your help. Thanks for the detailed clarification.
The solution is less complicated than I first thought. (...like almost ever)

regards
Michael

Printing problem
 

Michael Moeller submitted this idea :
GS wrote:
Michael,

This code was created in Excel's VBA (Visual Basic for Applications)
editor (VBE). If you're running MS Office on a Unix machine then I
suspect it can be accessed via Alt+F11. VBA is the macro language used
by all MSO apps.

The code sample IS a working example 'as is'! You may need to define
your Print_Area to be the same as its RangeToPrint, as well as do
whatever page setup desired so your printer receives the right
instruction for your desired results.
...
----------------

Garry,

I greately appreciate your help. Thanks for the detailed clarification.
The solution is less complicated than I first thought. (...like almost ever)

regards
Michael

You're welcome!
I failed to include that you could invoke PrintPreview so you can see
what's being printed AND offer you the ability to adjust print
settings. To do that just edit 'PrintMyCols' as follows:

ActiveSheet.PrintOut Preview:=True

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Printing problem
 

GS wrote:
Michael Moeller submitted this idea :
GS wrote:
Michael,

This code was created in Excel's VBA (Visual Basic for Applications)
editor (VBE). If you're running MS Office on a Unix machine then I
suspect it can be accessed via Alt+F11. VBA is the macro language used
by all MSO apps.

The code sample IS a working example 'as is'! You may need to define
your Print_Area to be the same as its RangeToPrint, as well as do
whatever page setup desired so your printer receives the right
instruction for your desired results.
...
----------------

Garry,

I greately appreciate your help. Thanks for the detailed clarification.
The solution is less complicated than I first thought. (...like almost
ever)

regards
Michael

You're welcome!
I failed to include that you could invoke PrintPreview so you can see
what's being printed AND offer you the ability to adjust print settings.
To do that just edit 'PrintMyCols' as follows:

ActiveSheet.PrintOut Preview:=True

Garry,

I don't want to try your patience to far but...
I just tested the macro on an actual sheet. Something strange happend
which may have another reason but the macro.

Data in fact is A to U, at 64 rows. I mark A to K at 20 rows as my print
area, and execute the macro.

The printer starts and on the screen all columns (execpt A and B) up to
"BJ" get hidden. The printer menu lists 10 pages to print. Next the
printer stops in the midst of the first page and I have to reset the
whole thing manually.

Apart from the fact that printing is an issue since decades, do you have
any idea?

regards
Michael

Printing problem
 

Michael Moeller was thinking very hard :
Data in fact is A to U, at 64 rows. I mark A to K at 20 rows as my print
area, and execute the macro.

The printer starts and on the screen all columns (execpt A and B) up to
"BJ" get hidden. The printer menu lists 10 pages to print. Next the
printer stops in the midst of the first page and I have to reset the
whole thing manually.

Apart from the fact that printing is an issue since decades, do you have
any idea?

Well, I tested this with 60 rows of data through cols A to U, and I see
where the problem lies. The code only works for the 5 col sample.
Change the following line in 'PrintMyCols'...

lMin = PrintRange.Columns.Count - RepeatRange.Columns.Count

to...
lMin = RepeatRange.Columns.Count + 1

It should now accomodate any number of columns/rows.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Printing problem
 

I suspect you also want to unhide all the columns after printing and so
to do so insert the following line before ending 'PrintMyCols'...

PrintRange.EntireColumn.Hidden = False

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Printing problem
 

GS laid this down on his screen :
I suspect you also want to unhide all the columns after printing and so to do
so insert the following line before ending 'PrintMyCols'...

PrintRange.EntireColumn.Hidden = False

Revised code to scroll the window...
'...
PrintRange.EntireColumn.Hidden = False
With ActiveWindow
.ScrollColumn = 1: .ScrollRow = 1
End With
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Printing problem
 

Another approach to the printing issue is to have each iteration of the
loop copy the data to a temp sheet and insert pagebreaks after each
one. This would allow you to do 1 print job consisting of 19 pages.
(Exact #pages depends on your FontSize and relative RowHeight. My
PrintPreview showed all 60 rows on a single page, with room for more.
My standard font is Arial and its FontSize is 8. I also use 1/2"
margins all around)

I use this approach to batch print recurring invoices, inputting only
the first invoice number and the billing date. I also set PrintOut
Preview:=True so I can check things before committing the print job.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Printing problem
 

GS wrote:
GS laid this down on his screen :
I suspect you also want to unhide all the columns after printing and
so to do so insert the following line before ending 'PrintMyCols'...

PrintRange.EntireColumn.Hidden = False

Revised code to scroll the window...
'...
PrintRange.EntireColumn.Hidden = False
With ActiveWindow
.ScrollColumn = 1: .ScrollRow = 1
End With
End Sub

Garry,

the hide/show thing works, but the main problem persists. Now it tries
to print all columns up to "AH" (same setup as before). Why "AH"? Even
more strange: setting a print area has no effect. The result is always
the same.

regards
Michael

Printing problem
 

After serious thinking Michael Moeller wrote :
GS wrote:
GS laid this down on his screen :
I suspect you also want to unhide all the columns after printing and
so to do so insert the following line before ending 'PrintMyCols'...

PrintRange.EntireColumn.Hidden = False

Revised code to scroll the window...
'...
PrintRange.EntireColumn.Hidden = False
With ActiveWindow
.ScrollColumn = 1: .ScrollRow = 1
End With
End Sub

Garry,

the hide/show thing works, but the main problem persists. Now it tries
to print all columns up to "AH" (same setup as before). Why "AH"? Even
more strange: setting a print area has no effect. The result is always
the same.

regards
Michael

Michael,

Did you replace the line that initializes 'lMin' as instructed? I got
consistent results after I edited this line of code, and PrintPreview
displayed as expected; "A:B" + 1 each of C to U (19 total). (No
PageSetup was done, meaning I used whatever default settings were in
place)

Did you read my reply about batch printing? Would you like to try that
approach? This does 1 19-page printout.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Printing problem
 

GS wrote:
After serious thinking Michael Moeller wrote :
GS wrote:
GS laid this down on his screen :
I suspect you also want to unhide all the columns after printing and
so to do so insert the following line before ending 'PrintMyCols'...

PrintRange.EntireColumn.Hidden = False

Revised code to scroll the window...
'...
PrintRange.EntireColumn.Hidden = False
With ActiveWindow
.ScrollColumn = 1: .ScrollRow = 1
End With
End Sub

Garry,

the hide/show thing works, but the main problem persists. Now it tries
to print all columns up to "AH" (same setup as before). Why "AH"? Even
more strange: setting a print area has no effect. The result is always
the same.

regards
Michael

Michael,

Did you replace the line that initializes 'lMin' as instructed? I got

yes, I did

consistent results after I edited this line of code, and PrintPreview
displayed as expected; "A:B" + 1 each of C to U (19 total). (No
PageSetup was done, meaning I used whatever default settings were in place)

I could easily imagine there is something wrong with my sheet other than
the macro. I know of some subtle errors almost impossible to find.

Did you read my reply about batch printing? Would you like to try that
approach? This does 1 19-page printout.

I have to postpone this. Next week I'll have more time.

regards
Michael

Printing problem
 

Michael,
I have the code in a standard module, NOT in the sheet.

Here's the code to try batch printing when you get time...

Sub BatchPrintMyCols()
'//Declare variables & type
Dim PrintRange As Range, RepeatRange As Range, PrintCol As Range
Dim i As Long, lMin As Long
Dim lRows As Long, lCols As Long, lStartRow As Long
Dim wksSource As Worksheet, wksTarget As Worksheet

'Get a reference to the sheet containing the source data
Set wksSource = ActiveSheet
'Get the total number of rows/cols to print
With wksSource.UsedRange
lRows = .Rows.Count
lCols = .Columns.Count
End With

'Get a reference to the cols to repeat on each printout.
'**The first row of each column should be user selected.
'**Columns should be contiguous.
Set RepeatRange = Selection.Resize(lRows)
'Get the size of the area to print
Set PrintRange = RepeatRange.Resize(, lCols)

'Get the first column to start with
lMin = RepeatRange.Columns.Count + 1
lStartRow = 1 '//initial position

Application.ScreenUpdating = False '//hide activity
'Get a reference to the batch print sheet
Set wksTarget = ActiveWorkbook.Sheets.Add

'Loop thru each column of the area to be printed
For i = lMin To PrintRange.Columns.Count
'Display progress
Application.StatusBar = "Preparing Page " _
& i - RangeToRepeat.Columns.Count _
& " of " _
& lCols - RangeToRepeat.Columns.Count

'Setup the batch print sheet
Set PrintCol = wksSource.Cells(1, i).Resize(lRows, 1)
Setup_BatchPrintJob wksTarget, lStartRow, RepeatRange, _
PrintCol, i, lCols
lStartRow = lStartRow + lRows 'setup next position
Next 'print job
Application.StatusBar = ""

'Print
wksTarget.PrintOut Preview:=True

'Cleanup
Application.DisplayAlerts = False
wksTarget.Delete
Application.DisplayAlerts = True
End Sub

Sub Setup_BatchPrintJob(SheetToPrint As Worksheet, _
StartRow As Long, _
RangeToRepeat As Range, _
ColToPrint As Range, _
CurrentCol As Long, _
LastCol As Long)
With SheetToPrint
'Start a new page if not the first page
If StartRow 1 Then _
.Cells(StartRow, 1).PageBreak = xlPageBreakManual
RangeToRepeat.Copy .Cells(StartRow, 1)
ColToPrint.Copy .Cells(StartRow, RangeToRepeat.Columns.Count + 1)
End With
Application.CutCopyMode = False
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Printing problem
 

GS was thinking very hard :
'Display progress
Application.StatusBar = "Preparing Page " _
& i - RangeToRepeat.Columns.Count _
& " of " _
& lCols - RangeToRepeat.Columns.Count

The above was cut from 'Setup_BatchPrintJob' and pasted into
'BatchPrintMyCols' at the last moment, and so some variables belong to
the former procedure. Change this as follows...

'Display progress
Application.StatusBar = "Preparing Page " _
& i - RepeatRange.Columns.Count _
& " of " _
& lCols - RepeatRange.Columns.Count

Sorry about posting that before making the changes!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Printing problem
 

GS was thinking very hard :
'Display progress
Application.StatusBar = "Preparing Page " _
& i - RangeToRepeat.Columns.Count _
& " of " _
& lCols - RangeToRepeat.Columns.Count

The above was cut from 'Setup_BatchPrintJob' and pasted into
'BatchPrintMyCols' at the last moment, and so some variables belong to
the former procedure. Change this as follows...

'Display progress
Application.StatusBar = "Preparing Page " _
& i - RepeatRange.Columns.Count _
& " of " _
& lCols - RepeatRange.Columns.Count

Sorry about posting that before making the changes!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc