Even disbursement of A single row into multiple columns

[robcbaker]

I was curious if you could assist me?
What I need to do is be able to take a row of data in a column such as
1
2
3
4
5
.
.
.
20000
and have a Macro that allows me to take that data and tell excel (for instances) I want to disburse the data over columns and them to be equal in each column such as below:
Column C
1
2
...
197
Then the Macro would grab the next 197 lines and put it into Column D and so on until all of the 20000 rows of data were put evenly into columns of not more than 197 rows
The 197 is not a set number so I would need to be able to change that variable. Any help is greatly appreciated on this.

 

[stnkynts]

Try this idea:

Option Explicit
Sub Dispersement()
Dim numcount As Variant
Dim rowcount As Long, diff As Long, icount As Long, Count As Long
Dim Msg As String
numcount = Application.InputBox("How many values do you want per column?", "Rows")
rowcount = Range("A" & Rows.Count).End(xlUp).Row
'err handling
If numcount = False Then
    Exit Sub
ElseIf Not IsNumeric(numcount) Then
    Msg = MsgBox("You must enter a numeric value.", vbOKOnly, "Numbers Only")
    Exit Sub
ElseIf numcount > rowcount Then
    Msg = MsgBox("You have entered a number that is greater than the available data.", vbOKOnly, "Error")
    Exit Sub
End If
diff = rowcount / numcount
diff = Application.WorksheetFunction.RoundDown(diff, 1)
Range("A1:A" & numcount).Copy Destination:=Range("B1")
Count = 1
For icount = 1 To diff
    Range("A" & (numcount * icount) + 1, "A" & numcount * (icount + 1)).Copy
    Range("B1").Offset(0, Count).PasteSpecial
    Count = Count + 1
Next icount
Application.CutCopyMode = False
End Sub

I assumed that you data set is in column A and that you wanted it evenly dispersed starting at column B. If prompts for an input box so you can select how many you want per row. Let me know.

 

[stnkynts]

Shucks I just realized you said a single row into multiple columns. I thought you meant a single column into multiple columns. I'll try to rework it and get back to you. Sorry for the confusion.

 

[Ruddles]

What I need to do is be able to take a row of data in a column such as
1
2
3
4
5

Stnkynts, hang fire: I think you were right the first time.