VBA Setting Page Break to Far Right

[bbalch]

Hello everyone.

I have a workbook with 50 + tabs and I use VBA to loop through and format each tab. Part of the VBA sets the workbook to page break view, drags the page breaks to the far right, and then switches back to normal view. This code takes the dotted blue line that runs vertically between two columns and drags it off to the far right (between column F and G). The code works great until it comes to a tab that does not have a dotted vertical blue line in page break view. I receive a Run-time error '9': Subscript out of range error message when this happens.

Any suggestions on how to: move to the next tab when this error occurs or how to modify the code below to set the page break between columns F and G?

Thanks in advance for your help.
Ben

    ' Change to page break view - drag off page breaks
    ActiveWindow.View = xlPageBreakPreview
    ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
        
    ' change to normal view
    ActiveWindow.View = xlNormalView

 

[Worf]

Hello Ben

- A dotted blue line is an automatic page break.
- If there are no vertical page breaks and the code tries to reference them, an error occurs.
- The examples below show how to delete and move page breaks.

Sub Delpbreaks()
Dim ws As Worksheet
Set ws = ActiveSheet
ActiveWindow.View = xlPageBreakPreview
If ws.VPageBreaks.Count > 0 Then ws.VPageBreaks(1).DragOff xlToRight, 1
'ActiveWindow.View = xlNormalView
End Sub


Sub Movepbreak()
Dim ws As Worksheet
Set ws = ActiveSheet
ActiveWindow.View = xlPageBreakPreview
Set ws.VPageBreaks(1).Location = [g1]       ' change position
End Sub

 

[bbalch]

That did the trick! Thank you.

The VBA I use is a combination of things I've found and pieced together with bits and pieces of VBA from the macro recorder. When you use the macro recorder and make changes to the page layout you end up with a lot of junk code. I was able to delete and make a change to the page layout code to get the same effect.

.FitToPagesWide = 1

This code was set to xlAutomatic.