I would enter/insert my new numbers and prices in any order in two columns on a
sheet named "Entry"

These would then be sorted and copied to a sheet named "Copyto" which would have
the format you want.

Try this kludge for starters.

Assumes "Entry" is activesheet when macro is run.

Sub Move_Sets_Two_Column()
Dim iSource As Long
Dim iTarget As Long
Set wksht = Worksheets("Copyto")
Set wksht2 = Worksheets("Entry")
With wksht
.Cells.ClearContents
End With
wksht2.Range("A:B").Sort Key1:=Range("A1"), Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

iSource = 1
iTarget = 1

Do
Cells(iSource, "A").Resize(20, 2).Copy _
Destination:=wksht.Cells(iTarget, "A")
Cells(iSource + 20, "A").Resize(20, 2).Copy _
Destination:=wksht.Cells(iTarget, "C")
Cells(iSource + 40, "A").Resize(20, 2).Copy _
Destination:=wksht.Cells(iTarget, "E")

iSource = iSource + 60
iTarget = iTarget + 21
Loop Until IsEmpty(Cells(iSource, "A").Value)

End Sub


Gord

On Thu, 11 Oct 2007 13:19:01 -0700, bilsons
wrote:

Sorry, should have clarified. I need a total of 6 columns per page, not 3.
They would be grouped as the first two columns would contain one column for
part number, one column for price. The other columns would be arranged in
the same manner.

IE:

column 1 / column 2 / column 3 / column 4 / column 5 / column 6
part#1 / price part#10 / price part#19 /
price
part#2 / price part#11 / price part#20 /
price
part#3 / price part#12 / price part#21 /
price
part#4 / price part#13 / price part#22 /
price
part#5 / price part#14 / price part#23 /
price
part#6 / price part#15 / price part#24 /
price
part#7 / price part#16 / price part#25 /
price
part#8 / price part#17 / price part#26 /
price
part#9 / price part#18 / price part#27 /
price



"Gord Dibben" wrote:

In which column are the part numbers entered?

You must have a column for those in order to sort columns by part number.

Post back then we can deal with ordering the 1-20, 21-40 etc.


Gord Dibben MS Excel MVP

On Wed, 10 Oct 2007 09:39:00 -0700, bilsons
wrote:

I am trying to create a multiple page pricelist in Excel. The problem I have
is that I need to have three columns with 20 rows in each column per page. I
need the pricelist to be sequential on each column on each page. For
example, the first column would list prices for parts 1 through 20 in order
of the part number, then the second column would list the prices for parts 21
through 40, and the third column would list the prices for parts 41 through
60. Page two would start with parts 61-80, and so on. The problem I have is
that if I try to insert a new cell for a new part, it shifts every cell in
that column down on every page, which makes them out of order. Does anyone
know how to set up the spreadsheet so that values are entered in x cell to x
cell, then force the next value to move to column two, cell x through cell x,
then to column three, cell x through cell x, then on to page two, column one,
cell x through x, etc.?