VBA to distribute data in column into columns and rows

I have a spreadsheet attached that contains data in column A (sheet "Column"). I would like to know if I can use VBA fill the data into a table that is five columns wide to make it look like (sheet "Distributed'). The quantity of items in column A will vary so the number of rows on the "Distributed" sheet will also vary. Eventually this table will be copied into a Word document.

Thanks, but here is my problem. I need the data spread out into exactly five columns. I don't care how many rows, but the # of columns must be five, and the number of items in column A will vary. I guess I could find that out by counting the number of rows? #2 would work if I knew how many cells to select for each column and if I could limit it to five columns.

After looking at the potential data I will see in this report and also the size limitations of the pages it will be inserted into in Word, I have a question. Could Part 2 above be modified to set the number of cells per group to 35 and limit the number of columns created to six? (I know I said five, but I tested and I have room for six). Then if there was additional data in the column that would not fit into that size range, it would skip a row and create a second range and so on for the remaining data. That would make it easy to select blocks of numbers that could be inserted into the word document.

Did some more searching and found a solution here:

http://www.pcreview.co.uk/forums/urg...-t1035328.html

Modified the code slightly to the following and it works:

Please Login or Register  to view this content.