Inserting a Column Break in Excel/Microsoft 365

jamiemarie

Board Regular
Good Morning, This problem probably has a super simple solution that I just keep looking over. I've tried searching in Excel's search bar and also Google and have had no luck. I found the term Column Break, but can not find anything other than a page break in my version of Excel.

I have a list of part numbers that I need to print. I am copying them from a worksheet that has a lot more information on it, so the numbers are in one column spanning several print pages. Is there an easy way to copy the part numbers to the new sheet where it will stop at the page break and go to the next column? I have attached a picture of what I have right now, and a second picture of what I would like to have since I'm not sure if I'm explaining very well. I would just like an easy way to get the results shown in picture two without having to cut and paste over and over.

Thank you so much for your time, and if I wasn't clear on anything I will be as prompt as possible answering any questions.

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Board Regular
Hi dear,

Please find if the formula in the attached file satisfies your requirement

assuming that the information is in column A, and the listing starts at cell C1
=IF(COLUMN()=3,IF(ROW()<=10,\$A1,""),IF(INDIRECT("A"&ROW()+10)=0,"",INDIRECT("A"&ROW()+10)))

COLUMN()=3 reflect column C, adjust where required
ROW()<=10 & ROW()+10 reflects number of data points in each column, adjust to your requirement

simply drag across the columns/rows

Best Regards
M. Yusuf

jamiemarie

Board Regular
Thank you for your reply. I tried the formula, and I'm a little confused. I was able to drag the first and second columns fine, but then the rest of the page ended up being a repeat of column 3?

I have downloaded XL2BB, here is what I could do with the formula.

PERSONAL.XLSB
ABCDEFGHI
1ITEMIDK-2716ITEMIDK-2716K-2716K-2716K-2716K-2716K-2716
2HW-001K-2740HW-001K-2740K-2740K-2740K-2740K-2740K-2740
3HW-002K-2741HW-002K-2741K-2741K-2741K-2741K-2741K-2741
4HW-006K-2957HW-006K-2957K-2957K-2957K-2957K-2957K-2957
5HW-008K-402HW-008K-402K-402K-402K-402K-402K-402
6K-1111K-4080K-1111K-4080K-4080K-4080K-4080K-4080K-4080
7K-1112K-4081K-1112K-4081K-4081K-4081K-4081K-4081K-4081
8K-1113K-4082K-1113K-4082K-4082K-4082K-4082K-4082K-4082
9K-1114K-4083K-1114K-4083K-4083K-4083K-4083K-4083K-4083
10K-1115K-4084K-1115K-4084K-4084K-4084K-4084K-4084K-4084
11K-1154K-4085K-1154K-4085K-4085K-4085K-4085K-4085K-4085
12K-118K-4086K-118K-4086K-4086K-4086K-4086K-4086K-4086
13K-121K-4087K-121K-4087K-4087K-4087K-4087K-4087K-4087
14K-124K-4088K-124K-4088K-4088K-4088K-4088K-4088K-4088
15K-127K-4089K-127K-4089K-4089K-4089K-4089K-4089K-4089
16K-130K-4090K-130K-4090K-4090K-4090K-4090K-4090K-4090
17K-133K-4091K-133K-4091K-4091K-4091K-4091K-4091K-4091
18K-136K-4097K-136K-4097K-4097K-4097K-4097K-4097K-4097
19K-139K-4098K-139K-4098K-4098K-4098K-4098K-4098K-4098
20K-142K-4099K-142K-4099K-4099K-4099K-4099K-4099K-4099
21K-145K-4100K-145K-4100K-4100K-4100K-4100K-4100K-4100
22K-148K-4101K-148K-4101K-4101K-4101K-4101K-4101K-4101
23K-151SS-0013K-151SS-0013SS-0013SS-0013SS-0013SS-0013SS-0013
24K-154SS-0014K-154SS-0014SS-0014SS-0014SS-0014SS-0014SS-0014
25K-1577SS-0015K-1577SS-0015SS-0015SS-0015SS-0015SS-0015SS-0015
26K-1580SS-0016K-1580SS-0016SS-0016SS-0016SS-0016SS-0016SS-0016
27K-1583SS-0106K-1583SS-0106SS-0106SS-0106SS-0106SS-0106SS-0106
28K-1586SS-0110K-1586SS-0110SS-0110SS-0110SS-0110SS-0110SS-0110
29K-1589SS-0111K-1589SS-0111SS-0111SS-0111SS-0111SS-0111SS-0111
30K-1592SS-0113K-1592SS-0113SS-0113SS-0113SS-0113SS-0113SS-0113
31K-1595SS-0114K-1595SS-0114SS-0114SS-0114SS-0114SS-0114SS-0114
32K-1598SS-0120K-1598SS-0120SS-0120SS-0120SS-0120SS-0120SS-0120
33K-1601SS-0125K-1601SS-0125SS-0125SS-0125SS-0125SS-0125SS-0125
34K-1722SS-0142K-1722SS-0142SS-0142SS-0142SS-0142SS-0142SS-0142
35K-1725SS-0162K-1725SS-0162SS-0162SS-0162SS-0162SS-0162SS-0162
36K-1918SS-0164K-1918SS-0164SS-0164SS-0164SS-0164SS-0164SS-0164
37K-1921SS-0170K-1921SS-0170SS-0170SS-0170SS-0170SS-0170SS-0170
38K-1924SS-0180K-1924SS-0180SS-0180SS-0180SS-0180SS-0180SS-0180
39K-1930SS-0181K-1930SS-0181SS-0181SS-0181SS-0181SS-0181SS-0181
40K-1933SS-0184K-1933SS-0184SS-0184SS-0184SS-0184SS-0184SS-0184
41K-1936SS-0207K-1936SS-0207SS-0207SS-0207SS-0207SS-0207SS-0207
42K-2711SS-0209K-2711SS-0209SS-0209SS-0209SS-0209SS-0209SS-0209
43K-2712SS-0239K-2712SS-0239SS-0239SS-0239SS-0239SS-0239SS-0239
44K-2713SS-0240K-2713SS-0240SS-0240SS-0240SS-0240SS-0240SS-0240
45K-2714SS-0241K-2714SS-0241SS-0241SS-0241SS-0241SS-0241SS-0241
46K-2715SS-0245K-2715SS-0245SS-0245SS-0245SS-0245SS-0245SS-0245
47K-2716SS-0246 SS-0246SS-0246SS-0246SS-0246SS-0246SS-0246
Sheet1
Cell Formulas
RangeFormula
B1:I47B1=IF(COLUMN()=3,IF(ROW()<=46,\$A1,""),IF(INDIRECT("A"&ROW()+46)=0,"",INDIRECT("A"&ROW()+46)))

jamiemarie

Board Regular
Found A Solution!

In case anyone else comes across this thread. I found a macro that Tom Urtis posted on this board in 2014! So thank you so much Tom! His formula is below for anyone else that needs it. I just changed the # of rows I needed to suit my needs.

VBA Code:
``````Sub Transfer100()
Application.ScreenUpdating = False
Dim LastRow&, NextRow&, NextColumn&
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
NextRow = 101
NextColumn = 2
Do
Range(Cells(NextRow, 1), Cells(NextRow + 99, 1)).Cut Cells(1, NextColumn)
NextRow = NextRow + 100
NextColumn = NextColumn + 1
Loop Until NextRow > LastRow
Application.ScreenUpdating = True
End Sub``````

jamiemarie

Board Regular
Ok Ok, Last time I'm updating this thread,. It was just super frustrating trying to figure this out as a newbie, especially when I was stuck on googling the wrong terms. If anyone else like me happens upon this thread, I want them to be as happy as I am to have a solution.

So I was able to modify Tom's macro to get exactly what i wanted, which is have one long column stop at the end of the print area and start in the next column while also having the header over each column. It may be sloppy, but it works.

VBA Code:
``````Sub Split_Column()
Application.ScreenUpdating = False
Dim LastRow&, NextRow&, NextColumn&
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
NextRow = 47
NextColumn = 2
Do
Range(Cells(NextRow, 1), Cells(NextRow + 46, 1)).Cut Cells(1, NextColumn)
NextRow = NextRow + 46
NextColumn = NextColumn + 1
Loop Until NextRow > LastRow
Application.ScreenUpdating = True

Range("B1").Select
Selection.Cut
Range(Selection, Selection.End(xlDown)).Select
Range("A47").Select
ActiveSheet.Paste
Range("A1").Select
Selection.Copy
Range("B1:S1").Select
ActiveSheet.Paste
End Sub``````

All you have to do is change how many rows you need and your range for your header.

Here is a screenshot of the end results.

Replies
1
Views
91
Replies
1
Views
93
Replies
3
Views
84
Replies
4
Views
129
Replies
2
Views
113