Automate print out entire workbook without setting the print area.

[Pradeep Kumar Gupta]

I have a file with 57 sheets.It is to be print out 4 times in a month and datas always change every time.
Every time,I select the range of each sheet and then print out.Can I get rid of this cumbersome excercise?
Please refer my attach file for more clarification.
In this file,I have taken a example of 4 sheets.
I want to take print out each sheet separately on A4 size paper till the last i.e.4 in this case.
But the following points are important before take a print out:-
1-Each sheet print out separately on a single page.
2-Range should be start from cell no.B4 and col.G till the last row of each sheet.
3-40 rows should be print out per page.More than 40 rows ,print out next page.But the Row no.5 should be repeat on every page.
4-Page setup-'Portrait' and Page size-'A4'.
Can it be possible through excel or any other best tool?

 

[kchiba]

Hi Pradeep,

You can set up a print routine in VBA, to select each sheet, clear and set the print area and print the file.

regards

kanti

 

[Pradeep Kumar Gupta]

Hi Kanti,

Perhaps,you have not read out my query .I want automation print out without select the print area option.If it is possible through VBA codes then,requested,how? Can you show me an example to print out automation,if possible?

 

[Hui]

A problem will be when somebody inadvertently places a character at row 1,024,000 etc

 

[kchiba]

Pradeep,

The macro selects the Print area, not the user

 

[kchiba]

Hui,

Would you do the honours and present your Print Macros workbook, I am not sure where to find it

cheers
kanti

 

[Pradeep Kumar Gupta]

Hi Kanti,

Pl.refer my attach file and have a look my VBA codes.These are not working fine.For an example,refer sheet 'AL01',printing is set up on 2 pages instead of 1 page.Similiar to sheet 'DC01'printing is set up on 6 pages instead of 4 pages.

 

[kchiba]

Hi Pradeep,

Try the attached file

cheers

kanti

 

[Pradeep Kumar Gupta]

Hi Kanti,

Now,the codes are working well only in that scenario where is no addition/deletion of the column.If we insert one or two column,then, the print area do not change but printing does not come on same page.
Pl.refer my attach file and sheet no.'AL01'.I have insert 3 more column but the printing is not coming according to the range showing.

 

[SirJB7]

Hi, Pradeep!

As long as you:
a) Have all your worksheets names of 4 char long
b) Neither have blank cells in column B nor row 5

Define this dynamic named range with workbook scope:
ReluctantPrintAreaTable: =DESREF(INDIRECTO(DERECHA(CELDA("nombrearchivo");4)&"!$B$4");;;CONTARA(INDIRECTO(DERECHA(CELDA("nombrearchivo");4)&"!$B$6:$B$1048576"))+2;CONTARA(INDIRECTO(DERECHA(CELDA("nombrearchivo");4)&"!$5:$5"))) -----> in english: =OFFSET(INDIRECT(RIGHT(CELL("filename"),4)&"!$B$4"),,,COUNTA(INDIRECT(RIGHT(CELL("filename"),4)&"!$B$6:$B$1048576"))+2,COUNTA(INDIRECT(RIGHT(CELL("filename"),4)&"!$5:$5")))

And then set a macro in any standard module (but not class modules of workbook and worksheets) that prints that named range.

Hope it helps.

Regards!

 

[kchiba]

Hi Pradeep

try the attached file

kanti

 

[Pradeep Kumar Gupta]

Hi Kanti,

Good Morning.

Yes that is the VBA codes which I want.Now,the codes are working fine.Glad.Thanks for the help.

 

[kchiba]

Thank you for the feedback.

 

[Hui]

Pradeep

You may also want to have a look at these two posts

http://chandoo.org/wp/2011/09/14/hui’s-excel-report-printer/
&/or
http://forum.chandoo.org/threads/print-macro-for-selected-sheets-from-200-worksheets.5795/

although they don't offer automatic range detection it could be incorporated if required

 

[SirJB7]

Hi, Pradeep Kumar Gupta!

Give a look at this file:
https://dl.dropboxusercontent.com/u...(for Pradeep Kumar Gupta at chandoo.org).xlsm

This is the code implementing the unique dynamic named range as of my 1st post:

Option Explicit

Sub PrintReluctantRanges()
    ' constants
    Const ksName = "ReluctantPrintAreaTable"
    Const ksTitles = "$4:$5"
    Const kiLines = 40
    Const kiPreview = True
    ' declarations
    Dim rng As Range
    Dim I As Integer, J As Integer
    ' start
    ' process
    With ActiveWorkbook
        For I = 1 To .Worksheets.Count
            With .Worksheets(I)
                .Activate
                Set rng = .Range(ksName)
                If Not rng Is Nothing Then
                    With .PageSetup
                        .PrintArea = rng.Address
                        .PrintTitleRows = ksTitles
                        .PrintTitleColumns = ""
                        .Orientation = xlPortrait
                        .Zoom = False
                        .PaperSize = xlPaperA4
                        .FitToPagesWide = 1
                        .FitToPagesTall = False
                    End With
                    .ResetAllPageBreaks
                    For J = 3 + kiLines To rng.Rows.Count - 1 Step kiLines
                        .HPageBreaks.Add .Cells(rng.Row + J - 1, 1)
                    Next J
                End If
                Set rng = Nothing
            End With
        Next I
        .Worksheets(1).Activate
        .PrintOut Preview:=kiPreview
    End With
    ' end
    Beep
End Sub

There're a few things that I think should be tweaked in the code version from previous post:
a) .Zoom = False
Missing, required for proper page fitting.
b) .PrintTitleColumns = ""
It was fixing columns B:G, is that right? So 1st page of a sheet with data up to column N will print A:G &K:L, 2nd one A:G & M:N, assuming there's space for 2 more columns than A:G.
c) ActiveWorkbook.PrintOut Copies:=1, Collate:=True (from previous code)
Collate applies for intercalate features, i.e., when more than one copy.

Hope it helps.

Regards!