Insert pages no.of Printing

User avatar
PRADEEPB270
3StarLounger
Posts: 354
Joined: 27 Oct 2013, 15:11
Location: Gurgaon INDIA

Insert pages no.of Printing

Post by PRADEEPB270 »

I have a file and want to insert the pages no. when print out the sheet from attach file macros,either from "Automateprinting" or from "Mannualprinting".
For an example,please refer my attach file,in this I have created the sheets like RD01 and want to print out this sheet.This sheet is going to be print out for 2 pages and want to automate mark the row no.4 on the top of the each relevant sheet and insert pages nos i.e.1 to 2 or 1 to 4.at bottom on the right.

How can modify ( Insert Pages Nos ) my vba Codes in "Automateprinting" and "Mannualprinting" macros?
Last edited by PRADEEPB270 on 18 Mar 2014, 08:19, edited 1 time in total.
Regards

Pradeep Kumar Gupta
INDIA

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Insert pages no.of Printing

Post by HansV »

Where exactly do you want the page numbers? Please provide precise and detailed information.
Best wishes,
Hans

User avatar
PRADEEPB270
3StarLounger
Posts: 354
Joined: 27 Oct 2013, 15:11
Location: Gurgaon INDIA

Re: Insert pages no.of Printing

Post by PRADEEPB270 »

At Bottom extreme right.Means,whenever a sheet printout goes to more than 1 page,then,it should mark as page 1 of 2,on next page 2 of 2 or so on,according to pages and it should be mark at the' right hand side bottom'.Also row no.4 of particular sheet should appear on every next page as its appear in first page.
Regards

Pradeep Kumar Gupta
INDIA

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Insert pages no.of Printing

Post by Rudi »

Hi,

The Auto Print and Manual Print is very similar... why have two macros?

The macro that was already in your workbook is slightly edited to add the page numbering, else runs OK and does what you need...

Code: Select all

Sub Printing()
    Dim wsh As Worksheet
    Dim rng As Range
    Dim r As Long
    Dim m As Long
    Application.PrintCommunication = False
    For Each wsh In Worksheets
        With wsh.PageSetup
            .Orientation = xlPortrait
            .PaperSize = xlPaperA4
            .PrintArea = wsh.UsedRange.Address
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = False
            .RightFooter = "&P of &N"
            .PrintTitleRows = wsh.UsedRange.Rows(1).Address
        End With
        With wsh.UsedRange
            m = .Row + .Rows.Count - 1
        End With
        r = 70
        wsh.ResetAllPageBreaks
        Do While r < m
            wsh.HPageBreaks.Add Before:=wsh.Range("B" & r)
            r = r + 70
        Loop
    Next wsh
    Application.PrintCommunication = True
End Sub
Edit: Sorry, just to add...
It assumes your row height is Excel standard row height within the print area
It is important that your list headings be ONE row in height, not over two or more rows.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
PRADEEPB270
3StarLounger
Posts: 354
Joined: 27 Oct 2013, 15:11
Location: Gurgaon INDIA

Re: Insert pages no.of Printing

Post by PRADEEPB270 »

Thanks Rudi for great help.Glad to find the editing in my codes.Macro is working perfect.
Regards

Pradeep Kumar Gupta
INDIA

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Insert pages no.of Printing

Post by Rudi »

One last thing:
If you don't need to fit a specific amount of records on one page, but just want to split the data at a natural page break so it fills the page, then use this code what does not insert manual page breaks after each 40th or 70th row...

Code: Select all

Sub Printing()
    Dim wsh As Worksheet
    Dim rng As Range
    Dim r As Long
    Dim m As Long
    Application.PrintCommunication = False
    For Each wsh In Worksheets
        wsh.ResetAllPageBreaks
        With wsh.PageSetup
            .Orientation = xlPortrait
            .PaperSize = xlPaperA4
            .PrintArea = wsh.UsedRange.Address
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = False
            .RightFooter = "&P of &N"
            .PrintTitleRows = wsh.UsedRange.Rows(1).Address
        End With
    Next wsh
    Application.PrintCommunication = True
End Sub
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
PRADEEPB270
3StarLounger
Posts: 354
Joined: 27 Oct 2013, 15:11
Location: Gurgaon INDIA

Re: Insert pages no.of Printing

Post by PRADEEPB270 »

Once again thanks Rudi for nice suggestion.
Regards

Pradeep Kumar Gupta
INDIA