Not strictly Excel. Help with numbering pages!

[eddy]

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

 

[Cbrine]

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]

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]

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]

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]

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]

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