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?
Insert pages no.of Printing
-
- 3StarLounger
- Posts: 354
- Joined: 27 Oct 2013, 15:11
- Location: Gurgaon INDIA
Insert pages no.of Printing
Last edited by PRADEEPB270 on 18 Mar 2014, 08:19, edited 1 time in total.
Regards
Pradeep Kumar Gupta
INDIA
Pradeep Kumar Gupta
INDIA
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Insert pages no.of Printing
Where exactly do you want the page numbers? Please provide precise and detailed information.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 354
- Joined: 27 Oct 2013, 15:11
- Location: Gurgaon INDIA
Re: Insert pages no.of Printing
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
Pradeep Kumar Gupta
INDIA
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Insert pages no.of Printing
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...
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.
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
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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 3StarLounger
- Posts: 354
- Joined: 27 Oct 2013, 15:11
- Location: Gurgaon INDIA
Re: Insert pages no.of Printing
Thanks Rudi for great help.Glad to find the editing in my codes.Macro is working perfect.
Regards
Pradeep Kumar Gupta
INDIA
Pradeep Kumar Gupta
INDIA
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Insert pages no.of Printing
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...
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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 3StarLounger
- Posts: 354
- Joined: 27 Oct 2013, 15:11
- Location: Gurgaon INDIA
Re: Insert pages no.of Printing
Once again thanks Rudi for nice suggestion.
Regards
Pradeep Kumar Gupta
INDIA
Pradeep Kumar Gupta
INDIA