How do I set cell order for columns on each page in Excel?

[Guest]

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

 

[Dave Peterson]

If you're not doing anything with that table except printing it, you could put
that table into MSWord. You can create columns (under Format|Columns), then
inserting/deleting elements into that table will do exactly what you want.

If you're really using that data in excel (although that layout sounds difficult
to use), I would put the data on a separate worksheet. I'd a two columns: Part
number and price.

Then I'd update that list in place and use a macro to layout the data on a
separate sheet. And I'd run that macro to recreate those "pretty table"
worksheets whenever I updated that giant list.

 

[Gord Dibben]

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

 

[Guest]

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]

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

 

[Guest]

Ok, let's pretend I'm an idiot and have never done this before
How in the world would I set this up? Is this creating a new macro? Can
you walk me through this as you would a beginner? Thanks!

 

[Gord Dibben]

Since you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Or Ron de Bruin's site for where to enter macros and code.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord