Convert 1 huge column in to mult column to fit on 1/2 pages

Good afternoon

I have a list of warehouse locs
which are in sort order
and when we export it to excel they all in column A and run down some times up to 10,000 rows

when we go to print it just places 1 column with 47 locs per page

we where trying and find a way so excel would place a set amount per page once it hit a set row number it would move the rest of the locs to column B then to column C and D
this way we can fill up the full page so now we have 282 locs per page this is a lot of less paper for us

right now we are just cutting from 48 to 94 and pasting in to column B and so on this is taking so long to do

Is there a way excel can do it I looking at text to columns but it does not seem to help cause we only have 1 set of numbers in column A

any help would be great or pointing me to a guide

JustinwB

Good afternoon AliGW

Thanks for moving this to the VBA part

Is that what you want?
(using PowerQuery [Get&Transform])

• Load to PQ
• Duplicate table
• add index to both tables
• Filter 1st table index, here less than or equal to (48)
• Filter 2nd table index, greater than (48)
• delete index columns
• Close&Load as connection
• Select destination cell
• From Workbook Queries select Load to than Table/Cell
• do the same for the second table

Try

Sub test()
    Dim i As Long
    Const myStep As Long = 47
    For i = 1 To Range("a" & Rows.Count).End(xlUp).Row Step myStep
        Cells(1, i \ myStep + 6).Resize(myStep).Value = Cells(i, 1).Resize(myStep).Value
    Next
End Sub

Thanks Jindon

this works great
edited a few things starting and Blank
and colors and print areas

you are the best

thanks for all your help
JustinwB

hi sandy666
Very sorry I might of explain my sample incorrectly with the layout

jindon VBA worked great

Jindon
I do have a question
how do I change the Column which drops all the data in to right now its Column F I like to change to H or C

if possible is there a way to add a 2nd blank column to each new coloumn that is created
e.g. column F text G blank H text I blank J blank and so on

Thanks for your time

Alter to suite

Sub test()
    Dim i As Long, t As Long, BlankCol As Long
    Const myStep As Long = 47
    t = 3  '<- starting column index
    BlankCol = 2  '<- number of blank columns
    For i = 1 To Range("a" & Rows.Count).End(xlUp).Row Step myStep
        Cells(1, t).Resize(myStep).Value = Cells(i, 1).Resize(myStep).Value
        t = t + 1 + BlankCol
    Next
End Sub

You are welcoe and thanks for the rep.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.