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

If column F needs a vertical pagebreak, you won't be able to move it to the
right

--
Regards,
Tom Ogilvy

"steveokur" wrote in message
om...
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


"Tom Ogilvy" wrote in message ...
If column F needs a vertical pagebreak, you won't be able to move it to the
right

--
Regards,
Tom Ogilvy

"steveokur" wrote in message
om...
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

You don't move pagebreaks. You delete them and insert them.

--
Regards,
Tom Ogilvy

steveokur wrote in message
om...
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


"Tom Ogilvy" wrote in message
...
If column F needs a vertical pagebreak, you won't be able to move it to
the
right

--
Regards,
Tom Ogilvy

"steveokur" wrote in message
om...
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

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

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

--
Regards,
Tom Ogilvy


Tom Ogilvy wrote in message
...
You don't move pagebreaks. You delete them and insert them.

--
Regards,
Tom Ogilvy

steveokur wrote in message
om...
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


"Tom Ogilvy" wrote in message
...
If column F needs a vertical pagebreak, you won't be able to move it
to
the
right

--
Regards,
Tom Ogilvy

"steveokur" wrote in message
om...
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

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

"Tom Ogilvy" wrote in message ...
Let me restate that as it could be misinterpreted. You delete them and add
them

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

--
Regards,
Tom Ogilvy


Tom Ogilvy wrote in message
...
You don't move pagebreaks. You delete them and insert them.

--
Regards,
Tom Ogilvy

steveokur wrote in message
om...
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


"Tom Ogilvy" wrote in message
...
If column F needs a vertical pagebreak, you won't be able to move it
to
the
right

--
Regards,
Tom Ogilvy

"steveokur" wrote in message
om...
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