Take one column of data and make it into multiple- Excel

[bamaisgreat]

In column A I have 2985 rows of data. I would like to take it and make it into multiple columns so i can print off a couple pages instead of 30.. Any suggestions

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also how many rows/columns do you want to end up with?

 

[bamaisgreat]

Thanks I would like about 40 rows with as many rows as it takes..

 

[Fluff]

40 rows or 40 columns?

 

[bamaisgreat]

Sorry about that. 40 rows with as many columns as it takes. Need data to do from left to right

 

[Fluff]

Ok, how about

=WRAPCOLS(A1:A2985,40)

 

[bamaisgreat]

Perfect Thanks

 

[Fluff]

Forgot to include the last argument

=WRAPCOLS(A1:A2985,40,"")

This will prevent the #N/A errors at the end of the data.

 

[ranman256]

run: Breakup2Ncols
tho you can , in print preview, set how to fit to page, and print 2 sided

Public Sub Breakup2Ncols()
Dim iTotRecs As Long, iCols As Long, iMaxRow As Long, c As Long, iSrcRow As Long, iTargRow As Long
Dim wsSrc As Worksheet, wsTarg As Worksheet
Dim vVal, vRet

Set wsSrc = ActiveSheet

vRet = InputBox("How many rows per page?", "Rows to use", 70)

If vRet = "" Then Exit Sub
If Not IsNumeric(vRet) Then Exit Sub

iMaxRow = Val(vRet)

Range("a1").Select
Selection.End(xlDown).Select
iTotRecs = ActiveCell.Row
Range("a1").Select
'iMaxRow = r \ iCols    'rows/col
iCols = iTotRecs \ iMaxRow

  'results sheet
Sheets.Add
Set wsTarg = ActiveSheet

iSrcRow = 1
wsSrc.Activate
While iSrcRow <= iTotRecs

   For c = 1 To iCols + 1
       'wsTarg.Cells(1, c).Select
       iTargRow = 1
       
       While iTargRow <= iMaxRow
          vVal = wsSrc.Cells(iSrcRow, 1).Value
          wsTarg.Cells(iTargRow, c).Value = vVal
          iTargRow = iTargRow + 1
          iSrcRow = iSrcRow + 1
       Wend
       vVal = wsSrc.Cells(iSrcRow, 1).Value
       iTargRow = 1
       wsTarg.Cells(iTargRow, c + 1).Value = vVal
   Next
   
Wend
wsTarg.Activate
Set wsSrc = Nothing
Set wsTarg = Nothing
MsgBox "Done"
End Sub