Moving Vertical Page Breaks in Excel via VB

  • Thread starter Thread starter steveokur
  • Start date Start date
S

steveokur

I have created code that incorporates multiple worksheets into one
overall worksheet.

The problem I am experiencing deals with Vertical Page Breaks. In the
final worksheet, 3 separate vertical page breaks are created. I cannot
figure out how to move them in code. I have tried recording a macro,
then copying the code, but that gives a run time error.

I need to move the vertical page break from after column F to after
column H.

I have included my code below for assistance.

Thanks - Steve

VB Code -->

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Sub Merge_Open_Issues()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Open Issues").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Application.ScreenUpdating = False
Set DestSh = Worksheets.Add
DestSh.Name = "Open Issues"
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> DestSh.Name Then
Last = LastRow(DestSh)
sh.Range("A1:H100").Copy
DestSh.Cells(Last + 2, 1).PasteSpecial xlPasteAll, ,
False, False
DestSh.Cells(Last + 2, 1).PasteSpecial (8), , False, False
sh.Range("A2:H100").AutoFilter Field:=7, Criteria1:="="
sh.Range("A1:H100").Copy
DestSh.Cells(Last + 2, 1).PasteSpecial xlPasteAll, ,
False, False
sh.Range("A2:H100").AutoFilter
End If
Next

Sheets("Open Issues").Rows.AutoFit
ActiveWindow.Zoom = 50
Worksheets(1).PageSetup.Orientation = xlLandscape

With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.37)
.RightMargin = Application.InchesToPoints(0.39)
.TopMargin = Application.InchesToPoints(0.54)
.BottomMargin = Application.InchesToPoints(0.55)
End With
Application.CutCopyMode = False
DestSh.Cells(1).Select
Application.ScreenUpdating = True
End Sub
 
What do you mean that the vertical pagebreak NEEDS to be a coulmn F? I
can manually move the page break with no problem. The sheet I am
creating is formatted for landscape, so that the entire worksheet is
printed on one 8.5x11 sheet of paper.

Steve
 
Let me restate that as it could be misinterpreted. You delete them and add
them

With Worksheets(1)
.VPageBreaks.Add .Range("G25")
End With
 
Tom,

I am a little confused. I am no VB whiz at all. But what coding do I
have to add to delete or remove the current page breaks and then add
the one vertical page break I want after column H.

Thanks - Steve
 
Back
Top