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

Peter wrote:

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

--

Dave Peterson

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

Dave Peterson wrote in message
...
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

Peter wrote:

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

--

Dave Peterson

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


Greg Wilson wrote in message ...
Is this what you're looking for?

Sub Numbers()
Dim i As Integer, ii As Integer
Dim iii As Integer, X As Integer

Cells.Clear
For i = 1 To 6 Step 5
For ii = 0 To 10 Step 5
For iii = 1 To 76 Step 15
X = X + 1
Cells(i, ii + iii) = X
Next iii
Next ii
Next i
End Sub

Regards,
Greg


-----Original Message-----
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




.

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

Greg Wilson wrote in message
...
Is this what you're looking for?

Sub Numbers()
Dim i As Integer, ii As Integer
Dim iii As Integer, X As Integer

Cells.Clear
For i = 1 To 6 Step 5
For ii = 0 To 10 Step 5
For iii = 1 To 76 Step 15
X = X + 1
Cells(i, ii + iii) = X
Next iii
Next ii
Next i
End Sub

Regards,
Greg


-----Original Message-----
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




.

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
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


Greg Wilson wrote in message
...
Is this what you're looking for?

Sub Numbers()
Dim i As Integer, ii As Integer
Dim iii As Integer, X As Integer

Cells.Clear
For i = 1 To 6 Step 5
For ii = 0 To 10 Step 5
For iii = 1 To 76 Step 15
X = X + 1
Cells(i, ii + iii) = X
Next iii
Next ii
Next i
End Sub

Regards,
Greg


-----Original Message-----
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




.