removing all page breaks such that excel>pdf only prints on page

0
votes

I have a bunch of sheets I wish to save as pdf. I'm able to do so, but it keeps splitting my data into random bits. I would like to make a code that will remove all the page breaks such that it prints the pdf as one page. Any ideas on how to do this? Thanks! Below is a code I'm trying to use to remove the page break before saving the sheets as pdf, but it's not working.

Edit: I've tried looping it such that it removes all page breaks, but I'm having trouble making it stop once there is no more page breaks. Can anyone help me to make the loop end once all page breaks are gone?

Sub pdf2()
'saving the answers as pdf
Dim x As Integer
Dim y As Integer
Dim mywsname As String
Workbooks("Book1").Activate 'select workbook where sheets are at
For y = 1 To Application.Sheets.Count 'loop through and save all sheets as pdf
Sheets(y).Select
mywsname = ActiveSheet.Name
x = 1 'attempt to remove all page breaks
ActiveWindow.View = xlPageBreakPreview
For x = 1 To ActiveSheet.VPageBreaks.Count + 2
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
Next x
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\TMSVC\docs\" & mywsname & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
Next y
End Sub
1
excelvba
I am afraid that exporting to pdf does that for each excel page. If you move the cursor in the bottom left side of the pdf (open) document, you can see the page size... Excel looks to not accept custom page sizes. It (maybe) can be possible to print it as pdf (Adobe PDF, if Acrobat installed) setting a custom page able to include all the page height. I do not know how 'Microsoft print to pdf' deals with such an issue. I never tried. I think that it should be better (safer) to analize somehow the structure of the sheet and set page breaks where suitable for a good appearance (in pdf)... – FaneDuru
.DisplayPageBreaks only changes the visibility of page breaks, it doesn't (to my knowledge) change anything for the print. – Christofer Weber
Might want to look at ActiveSheet.PageSetup.FitToPagesWide = 1 and ActiveSheet.PageSetup..FitToPagesTall = 1 if you just want to print it all on one page? You can try recording a macro while setting up the print page, and use that. – Christofer Weber
Hey man, thanks for the replies! FitToPages sadly doesn't work at all. I have also tried recording a macro, but my sheets vary in size so it doesn't work. Essentially, I wish to remove all page breaks such that it only prints it as one page. I imagine something like xlright and down is needed, instead of just recording a macro. Anyone able to guide me to the right direction? thx – spiritandtime
@FaneDuru yeah that seems to be the case, i haven't seen anyone using vba to do this- i guess everyone is manually dragging the page breaks to where they want them to be. – spiritandtime

1 Answers

1
votes

Solved my own question: just in case anyone is facing the same problem. This code will save existing sheet as pdf and remove all page breaks, loop and save all sheets in workbook as separate pdfs. The name of the pdfs will be the sheetname.

Sub pdf2()
'saving the answers as pdf
Dim x As Integer
Dim y As Integer
Dim mywsname As String
Workbooks("YourBook").Activate 'select workbook where sheets are at. **Please edit your workbook!**
For y = 1 To Application.Sheets.Count 'loop through and save all sheets as pdf
Sheets(y).Select
mywsname = ActiveSheet.Name

x = 1  'remove all page breaks
ActiveWindow.View = xlPageBreakPreview
Do
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
x = x + 1
Loop Until ActiveSheet.VPageBreaks.Count = 0

'save as pdf. **Please edit yourfilepath!**
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:yourfilepath" & mywsname & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
Next y
End Sub