Fitting 2 Columns / 20,000 cells on fewer pages as possible? (Printout)

[JumboCactuar]

Hi,
looking for some ideas on best way to achieve this

I have data in:
Columns A + B (Header A1 and B1) down to around row 20000 (max characters per cell is 15)

and i need to compress the data so its printable with row 1 repeating.
I thought of:
viewing in page break preview
copying everying under the line into column C/D and repeating.

It has to stay in order so the last cell on page 1 follows to next cell on page2

Any way to automate this with VBA? Or any better way of achieving the same

Appreciate any help

 

[fhqwgads]

copy paste your two columns into word (this may take a while)
select your top row, go to the table tools tab>layout>data and select repeat header rows
go to the layout tab (not the layout tab under table tools, the other layout tab)>page setup, select columns and increase the number of columns per page

hope that worked

 

[JumboCactuar]

copy paste your two columns into word (this may take a while)
select your top row, go to the table tools tab>layout>data and select repeat header rows
go to the layout tab (not the layout tab under table tools, the other layout tab)>page setup, select columns and increase the number of columns per page

hope that worked

Thanks I will try this

 

[JumboCactuar]

so difficult to achieve in Word due to constant crashing / not responding

managed to paste it in and got 5 columns per page, but resizing and making sure each row is same size is tricky
will have another attempt tomorrow

Probably better working with a small dataset till I can figure the right formatting

 

[fhqwgads]

try this in excel

Sub pagebreaktest1()
Dim pbrow As Long 'rows in a page
Dim i As Long
Dim pgtot As Integer 'total number of pages
Dim pgcount As Integer
Dim colnum As Integer

For Each Row In Rows
If Row.PageBreak = xlAutomatic Then
pbrow = Row.Row - 2
Exit For
End If
Next Row

pgtot = ActiveCell.SpecialCells(xlCellTypeLastCell).Row \ pbrow + 1

For pgcount = 2 To pgtot
Range("A" & 50 + 49 * (pgcount - 2) + 1, "B" & 50 + 49 * (pgcount - 1)).Copy Destination:=Cells(2, (pgcount - 1) * 2 + 1)
Range("A1:B1").Copy Destination:=Cells(1, (pgcount - 1) * 2 + 1)
Next pgcount

ActiveSheet.PageSetup.PrintArea = ""
ActiveSheet.PageSetup.PrintArea = Range(Rows(1), Rows(pbrow + 1)).Address


ActiveSheet.ResetAllPageBreaks
'********
colcount = 4 'customise number of desired "columns" per page
'********
For i = colcount * 2 To ActiveCell.SpecialCells(xlCellTypeLastCell).Column Step colcount * 2
ActiveSheet.VPageBreaks.Add Before:=Columns(i + 1)
Next i
End Sub

 

[fhqwgads]

wait sorry try this

Sub pagebreaktest1()
Dim pbrow As Long 'rows in a page
Dim i As Long
Dim pgtot As Integer 'total number of pages
Dim pgcount As Integer
Dim colnum As Integer

ActiveSheet.PageSetup.PrintArea = ""
ActiveSheet.ResetAllPageBreaks

For Each Row In Rows
If Row.PageBreak = xlAutomatic Then
pbrow = Row.Row - 2
Exit For
End If
Next Row

pgtot = ActiveCell.SpecialCells(xlCellTypeLastCell).Row \ pbrow + 1

For pgcount = 2 To pgtot
Range("A" & pbrow + 1 + pbrow * (pgcount - 2) + 1, "B" & pbrow + 1 + pbrow * (pgcount - 1)).Copy Destination:=Cells(2, (pgcount - 1) * 2 + 1)
Range("A1:B1").Copy Destination:=Cells(1, (pgcount - 1) * 2 + 1)
Next pgcount

ActiveSheet.PageSetup.PrintArea = Range(Rows(1), Rows(pbrow + 1)).Address

'********
colcount = 4 'customise number of desired "columns" per page
'********
For i = colcount * 2 To ActiveCell.SpecialCells(xlCellTypeLastCell).Column Step colcount * 2
ActiveSheet.VPageBreaks.Add Before:=Columns(i + 1)
Next i
End Sub

 

[JumboCactuar]

wait sorry try this

Sub pagebreaktest1()
Dim pbrow As Long 'rows in a page
Dim i As Long
Dim pgtot As Integer 'total number of pages
Dim pgcount As Integer
Dim colnum As Integer

ActiveSheet.PageSetup.PrintArea = ""
ActiveSheet.ResetAllPageBreaks

For Each Row In Rows
If Row.PageBreak = xlAutomatic Then
pbrow = Row.Row - 2
Exit For
End If
Next Row

pgtot = ActiveCell.SpecialCells(xlCellTypeLastCell).Row \ pbrow + 1

For pgcount = 2 To pgtot
Range("A" & pbrow + 1 + pbrow * (pgcount - 2) + 1, "B" & pbrow + 1 + pbrow * (pgcount - 1)).Copy Destination:=Cells(2, (pgcount - 1) * 2 + 1)
Range("A1:B1").Copy Destination:=Cells(1, (pgcount - 1) * 2 + 1)
Next pgcount

ActiveSheet.PageSetup.PrintArea = Range(Rows(1), Rows(pbrow + 1)).Address

'********
colcount = 4 'customise number of desired "columns" per page
'********
For i = colcount * 2 To ActiveCell.SpecialCells(xlCellTypeLastCell).Column Step colcount * 2
ActiveSheet.VPageBreaks.Add Before:=Columns(i + 1)
Next i
End Sub

Thanks it's almost spot on

My original page cuts off to page 2 after row 92
But whatever size/font/column width etc.. after running this code it sets page break at row 51

Current page settings:
Font: Calibri 12
Column width:16
Row Height: 16
Page setup: shrink to 55%
0 top/bottom margins

These give me 92 rows per page (default orientation)

 

[fhqwgads]

maybe try set the font and other formatting before running the code
other than that im not sure what else might work