Print problem - Very quick bit of VBA needed

S

SWFC Gav

New Member
Joined
Jul 20, 2004
Messages
29
Hi All

Firstly, I found this section of code in another post, and it works a treat. So, if your bit of code many thanks!

Sub Workbook_BeforePrint(Cancel As Boolean)
Dim x As Integer
Dim Copies
Copies = InputBox("How many copies would you like?")
If Copies = "" Then
Cancel = True
Exit Sub
End If
Application.EnableEvents = False
For x = 1 To Copies
With ActiveSheet
.PageSetup.LeftHeader = "Pre Pack " & x
.PrintOut
End With
Next x
Cancel = True
Application.EnableEvents = True
End Sub

Right, What this does is print out pages each with a unique page number on, but I want it to start at a specific page number.

Also I would like to change the size of font in the header, is this possible too?

Many thanks for your help.

Gav
 
Sort by date Sort by votes
To change the startign number, change For X = 1 to your starting number.

The second part is:

<font face=Tahoma>        <SPAN style="color:#007F00">'   Set Font and Size</SPAN>
        .LeftHeader = "&""Tahoma,Regular""&10"
        .CenterHeader = ""</FONT>

You can get most of the page setup criteria by recording a macro of File-->Page Setup.

Hope that helps,

Smitty
 
Upvote 0
Many thanks for your reply, but I know this is really basic, but I really dont know much about VBA. Where should I put those lines into the code???

Also, how can I set x to a number defined by a user?

I guess this would involve using an input form, then setting x to the number inputted by the user. I guess it would be something like

For x to x + copies

Help... Im really confused :cry:
 
Upvote 0
I added an input line to gather the users desired start page, make note of the comment for increasing the font size just after the line of code:

Code: 
Sub Workbook_BeforePrint(Cancel As Boolean)
Dim x As Integer
Dim Copies
Copies = InputBox("How many copies would you like?")
start = InputBox("At what number would you like to start?")
If Copies = "" Then
Cancel = True
Exit Sub
End If
Application.EnableEvents = False
For x = start To Copies
With ActiveSheet
.PageSetup.LeftHeader = "&16" & "Pre Pack " & x 'change "16" as suited
.PrintOut
End With
Next x
Cancel = True
Application.EnableEvents = True
End Sub
 
Upvote 0
John Walkenbach's Excel (version here) Power Programming with VBA is a great resource and less than $30 at Amazon.com.

You can check them out at www.j-walk.com

Smitty
 
Upvote 0
Yeah it is a great book but I found it a little bit too advanced for me. I am comfortable with Excel, but I need, quite literally a "dummies guide" to VBA. I am learning a bit more about it everyday, but it is just so so daunting. I hope I'm not alone!
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
VBA error in hidden module Error 32 bit to 64 bit?
2 3 4
Replies
35
Views
536
Micron
M
E
  • Question Question
VBA warning to print 1 page
2 3
Replies
23
Views
985
excellence
E
F
  • Question Question
Printing: File settings override code
Replies
1
Views
153
Flavien
F
F
  • Locked
  • Question Question
Print Settings VS Macro Print Settings
Replies
1
Views
155
Peter_SSs
Peter_SSs
E
  • Solved
VBA to print two separate ranges on one sheet of paper
Replies
3
Views
132
EssKayKay
E

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