Positioning Slips on a Page

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

Hi to the experts/developers

I have developed a routine to print slips, six to a (portrait) page (three
across and two down)
It works well. However when the pages are guillotined the slips need
sorting. When dealing wih a hundred or more the task is pretty onerous. I
wondered whether any one has had to deal with this problem before?

I would like to place them on the page so that when guillotined the slips
fall on top of each other in an easier-to-sort manner.

A partial extract from the routine:

Sub Numbers()
Dim myNumber As Integer
Dim Firstnumber As Integer
Dim LastNumber As Integer
Firstnumber = 1
LastNumber = 36
Sheets("Numbers").Select
Cells.Clear
For myNumber = Firstnumber To LastNumber
Cells(1 + (Abs(myNumber / 2 = myNumber \ 2)) * 10, 1 + (myNumber -
Firstnumber - _
(Abs(myNumber / 2 = myNumber \ 2))) * 2) = myNumber
Next myNumber
End Sub

This then lays them out as follows:
1 3 5 7 9 11 13 15 17

2 4 6 8 10 12 14 16 18

What I am looking for is something like:

1 7 13 2 8 14 3 9 15

19 25 31 20 26 32 21 27 33

Continuing to the right of the above
4 10 16 5 11 17 6 12 18

22 28 34 23 29 35 24 30 36
Etc

The maximum will be about 180 slips so they can go across the top but wouldn
't matter going down either.

Has anybody developed the maths to position the slips as above to facilitate
their guillotining?

Thanks
Peter Bircher
SA
 
How about this: You build one set of numbers (two rows), then let excel
autofill the rest?

I stuck the numbers in a nice contiguous range (easier to code and easier to
check (for me at least). Then I filled down a range.

Then I came back and inserted extra columns and extra rows. I added an extra
GroupsToMake variable. It seemed to work ok.

Sub numbers2()

Application.ScreenUpdating = False

Dim oRow As Long
Dim oCol As Long
Dim GroupsToMake As Long

Dim Firstnumber As Long
Dim LastNumber As Long


With Worksheets("Numbers")
.Cells.Clear
Firstnumber = 1
LastNumber = 36
GroupsToMake = 100

If (LastNumber - Firstnumber + 1) Mod 18 <> 0 Then
MsgBox "error: Wrong set of numbers!"
Exit Sub
End If

.Range("a1").Resize(1, 3) _
= Array(Firstnumber, Firstnumber + 6, Firstnumber + 12)
For oCol = 4 To 18
.Cells(1, oCol).Value = .Cells(1, oCol - 3).Value + 1
Next oCol

For oCol = 1 To 18
.Cells(2, oCol).Value = .Cells(1, oCol).Value + 18
Next oCol

Range("a1:R2").AutoFill _
Destination:=Range("A1:R" & GroupsToMake * 2), Type:=xlFillSeries

For oCol = 18 To 2 Step -1
.Columns(oCol).Resize(, 3).Insert
Next oCol

For oRow = GroupsToMake * 2 To 2 Step -1
.Rows(oRow).Resize(9).Insert
Next oRow

End With

Application.ScreenUpdating = True
End Sub
 
Thanks Dave
I will have a close look at your suggestion.
In order to make the problem more understandable I have simplified the
output - it really is quite a complex report with data gleaned from another
sheet. However, all I am trying to do is place the result in certain
positions such that the numbers 1, 2, 3 etc are printed in the same relative
position while 4, 5 and 6 are also in the same relative position a will 7,
8, and 9 be.

It will effectively print in groups of 36.

Peter
 
Thanks Greg
I will have a good look at your sugestion.
However I am not sure if it will do the job.

The problem is that I already have the numbered slips which print as set out below.
The first two pages will print as shown, each number represents the individuals' slip
A whole lot of data relavant to each person is printed under their number.

(Best to show the next bit in Courier 10pt)
============ ============
| 1 3 5 | | 7 9 11 |
| | | |
| 2 4 6 | | 8 10 12 |
| | | |
============ ============

Below is the intended printout (in sets of 36)
============ ============
| 1 7 13 | | 2 8 14 |
| | | |
| 19 25 31 | | 20 26 32 |
| | | |
============ ============

When guillotining (6 pages at a time), slip 1 would fall on slip 2 etc solving the problem of sorting all the slips.

Peter
 
Hi Greg

I have now had a chance to test your loop structure in my code and, it
really does do the job. I must confess that I was doubtfull at first!

Naturally, I have had to adapt my code quite a bit - but this was expected.

Thanks so much, Greg, for your time and effort.

Peter Bircher
 
Peter,

For what it's worth, I conducted a trial as follows:

1) I named a sheet "Slips" and another "Numbers".
2) I created 36 representative slips on Sheet("Slips")
using Excel's Autofil feature. To do this I typed the
following text (representing a slip) into Cell A1 through
Cell A6:
Slip 1
Name: Bob James
Salary: 38,400
Title: Mining Engineer
Employee Number: V4090
Division: Mining

3) I selected the typed text including an extra row; i.e.
Cells A1 to A7 were selected.
4) I dragged the

Sub GuillotineSetup()
Dim i As Integer, ii As Integer
Dim iii As Integer, X As Integer
Dim Resp As Integer

Application.ScreenUpdating = False
'***** Transfer slips and arrange *****
With Sheets("Numbers")
.Cells.Clear
.Columns.ColumnWidth = 25
End With

For i = 1 To 8 Step 7
For ii = 0 To 2
For iii = 1 To 21 Step 4
Sheets("Slips").Cells(X * 7 + 1,
1).CurrentRegion.Copy
With Sheets("Numbers")
.Paste .Cells(i, ii + iii)
End With
X = X + 1
Next iii
Next ii
Next i
Application.ScreenUpdating = True
'***** Print slips *****
Resp = MsgBox("Print sheets?", vbQuestion +
vbYesNo, "Guillotine Setup")
If Resp = 7 Then Exit Sub
With Sheets("Numbers")
.PrintOut From:=1, To:=5
End With
End Sub
-----Original Message-----
Thanks Greg
I will have a good look at your sugestion.
However I am not sure if it will do the job.

The problem is that I already have the numbered slips which print as set out below.
The first two pages will print as shown, each number
Click to expand...
represents the individuals' slip
A whole lot of data relavant to each person is printed under their number.

(Best to show the next bit in Courier 10pt)
============ ============
| 1 3 5 | | 7 9 11 |
| | | |
| 2 4 6 | | 8 10 12 |
| | | |
============ ============

Below is the intended printout (in sets of 36)
============ ============
| 1 7 13 | | 2 8 14 |
| | | |
| 19 25 31 | | 20 26 32 |
| | | |
============ ============

When guillotining (6 pages at a time), slip 1 would fall
Click to expand...
on slip 2 etc solving the problem of sorting all the slips.
 
Back
Top