Setting number of pages to be printed

S

ss002d6252

New Member
Joined
Mar 5, 2010
Messages
23
I have VBA code which sets a print area but for some reason the printpreview tacks a couple more pages on the bottom (Ive tried palying with the pages breaks and it still does it).

Is there a way , apart from setting a print area, to limit the printed pages to, say,only pages 1-5 of the 10 shown on the print preview ?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
Hi Peter,

What if I want to show my name in the header or footer for each sheet every time I print. What will be the code?

Thanks in advance.

Regards.
 
Upvote 0
Code: 
With ActiveSheet.PageSetup
        .LeftHeader = " "
        .CenterHeader = " "
        .RightHeader = " "
        .LeftFooter = " "
        .CenterFooter = " "
        .RightFooter = "   "
 
End With

Just add the texgt you want, or &P , &D for page numbers/dates etc
 
Upvote 0
Hi dantheman9,

Thanks for the quick reply. What I want is to apply the code for all the worksheets. Do you have any idea? I want to write a code in the "ThisWorkbook".

Thanks again.

Regards.
 
Upvote 0
Perhaps this

Code: 
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In ActiveWindow.SelectedSheets
    With ws.PageSetup
            .LeftHeader = " "
            .CenterHeader = " "
            .RightHeader = " "
            .LeftFooter = " "
            .CenterFooter = " "
            .RightFooter = "   "
    End With
Next ws
End Sub
 
Upvote 0
Three comments.

1. This would not necessarily get you header on each printed page. For example, if you start with all sheets without headers the user prints (with only one sheet selected) via File|Print...|Entire workbook (steps/wording varies slightly between Excel versions) then the header will only come on the page(s) of the active sheet.

To overcome that you would need something like this
Code: 
Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.PageSetup.CenterHeader = "name"
    Next ws
End Sub

2. Unless you are getting new sheets added or somebody is manually removing/changing the headers, it seems a bit redundant to be running this code every time any part of the workbook is printed, since the heading will already (most likely) be there.

3. You said you wanted your name in the header every time you printed. Of course all the suggestions will print your name, or whatever you put in the code, whenever, anybody prints from the workbook, not just you.
 
Upvote 0
Thank you so much for the detailed reply and time. I really appreciate it. :):):)

Regards.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Problems Printing Multiple Tabs with One Tab upside down and Backwards
Replies
5
Views
104
mdmilner
M
D
  • Question
Very weird print margin problem, need help !
Replies
0
Views
136
dicklim39
D
tlc53
  • Question
Don't print blank pages, where page breaks are contained between hidden rows
Replies
0
Views
617
tlc53
tlc53
E
  • Question
Print routines
2
Replies
13
Views
688
MARK858
MARK858
Y
  • Solved
Greyed Rows - print BLACK
Replies
3
Views
390
rollis13
rollis13

Forum statistics

Threads
1,215,050
Messages
6,122,868
Members
449,097
Latest member
dbomb1414

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top