Not strictly Excel. Help with numbering pages!

eddy

Well-known Member
Joined
Mar 2, 2002
Messages
521
Not too sure if this can be done in Excel. If not maybe someone can recomend a programme that can do it.
This is the problem:-
I have batches of 200 sheets of A4 paper. I want to print an incremental number on the sheets in batches of 4.
e.g The first four sheets have 100 printed in the top right hand corner, the next four sheets have 101, the next four sheets have 102 etc.
Tried to do with lots of programmes but so far failed. All help appreciated as usual.
Regards Ed
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
Add a button to your sheet and add this code to the macro. It should do the job for you.

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim count As Double
Dim PageHeader As Double
Dim PrintCount As Double

PageHeader = 100
PrintCount =1
For count = 1 To 50
ws.PageSetup.RightHeader = PageHeader
ws.PrintOut PrintCount, PrintCount + 3

PageHeader = PageHeader + 1
PrintCount = PrintCount + 4

Next count

End Sub
 

eddy

Well-known Member
Joined
Mar 2, 2002
Messages
521
Hi ...

Thanks for the quick response. Seem to be having a small problem, when I run the code I am getting error:-
Object Variable or With block variable not set
Is PageSetup the culprit?
In the de-bugger the line:-
ws.PageSetup.RightHeader = PageHeader is highlighted in yellow

Could some of the variables be cells on the workseet.

e.g Cell B4 = Start Number e.g. 100
Cell B5 = number of same numbers e.g 4

Thanks for all your help Ed
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
eddy said:
Hi ...
Could some of the variables be cells on the workseet.

e.g Cell B4 = Start Number e.g. 100
Cell B5 = number of same numbers e.g 4

Thanks for all your help Ed
Hi eddy:

Cell B4 and Cell B5 of a specific sheet, say named Admin or PrintSetup could hold the variables for StartNumber, and number of times the printed number is to be repeated.

Now couple of questions ...

1. on each sheet where the number is to be printed, can you have it in a specific cell on every sheet -- and if yes, what would that cell be?.

2. and can the number to be printed on the sheet stay there all the time, rather than appear there only at print time?
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416

ADVERTISEMENT

Seem to be having a small problem, when I run the code I am getting error:- Object Variable or With block variable not set
Eddy,

See this line in the macro:
Dim ws As Worksheet

You need to explicitly define “ws” with the Set command e.g.

ws = Sheets(1)

or:
ws = Worksheets(“Sheet1”)

The macro will look like this:

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim count As Double
Dim PageHeader As Double
Dim PrintCount As Double

Set ws = Sheets(1)

PageHeader = 100

Etc.

End sub

Alternatively, as the macro is using a Command Button from the Toolbox menu, put the macro in the Sheet1 module (or whatever sheet you are using) and remove all references to “ws” e.g.

Delete:
Dim ws As Worksheet

Amend:
ws.PageSetup.RightHeader = PageHeader
ws.PrintOut PrintCount, PrintCount + 3

to:
PageSetup.RightHeader = PageHeader
PrintOut PrintCount, PrintCount + 3

HTH

Mike
 

eddy

Well-known Member
Joined
Mar 2, 2002
Messages
521
Thanks Guys for all the help.

Mike
You were right macro now runs OK, but for some reason my printer sxeems to lock up (maybe a buffer problem somewhere), if you try and throw 200 prints at it. Will see if I can fix that.

Yogi
Sheet is labled PaulsPrint
1) Number could be I1 but the ws.PageSetup.RightHeader = PageHeader
statement seems to put the number in right place i.e top right hand corner
2) Number could stay there all the time. It would only be re-set on each
totally seperate print run.
I am not printing anything else. I am using pre-printed stationary and simply want to put a number in the top right hand corner.

Just realised that any text would have to be outside the A4 print range, therefor the ideal would be :-

In Cell M1 have a text string e.g. Order Number
In Cell M2 have the start number
In Cell M3 have the number of copies befor the number increments
In Cell M4 have the total number of pages to print.

Thanks Ted
 

eddy

Well-known Member
Joined
Mar 2, 2002
Messages
521
Re: Not strictly Excel. Help with numbering pages!
Thanks Guys for all the help.

Mike
You were right macro now runs OK, but for some reason my printer sxeems to lock up (maybe a buffer problem somewhere), if you try and throw 200 prints at it. Will see if I can fix that.

Yogi
Sheet is labled PaulsPrint
1) Number could be I1 but the ws.PageSetup.RightHeader = PageHeader
statement seems to put the number in right place i.e top right hand corner
2) Number could stay there all the time. It would only be re-set on each
totally seperate print run.
I am not printing anything else. I am using pre-printed stationary and simply want to put a number in the top right hand corner.

Just realised that any text would have to be outside the A4 print range, therefor the ideal would be :-

In Cell M1 have a text string e.g. Order Number
In Cell M2 have the start number
In Cell M3 have the number of copies befor the number increments
In Cell M4 have the total number of pages to print.

Thanks Ted
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,897
Messages
5,767,001
Members
425,393
Latest member
SushiJuice

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
Top