Copy Page Breaks

J

jwleonard

Dave,
That was the very first thing I tried, but when the sheets are groupe
you can't drag or insert page breaks. I don't know why it wouldn'
work, it seems if your page breaks were in the same place on eac
worksheet (mine were when I tested it) then it wouldn't cause an
problems.

I guess I am still looking for an answer from someone about my VBA ide
from above!

Thanks Again
 
D

Dave Peterson

I couldn't drag the pagebreaks in "page break preview" mode--with multiple
sheets selected.

But I could insert via insert|pagebreaks via the worksheet menubar.

And I could insert|pagebreaks in normal view, too (with grouped sheets).

With code:

Option Explicit
Sub testme()

Dim mstrWks As Worksheet
Dim wks As Worksheet
Dim hPB As HPageBreak
Dim vPB As VPageBreak

Set mstrWks = Worksheets("sheet1")

For Each wks In ActiveWorkbook.Worksheets
If wks.Name = mstrWks.Name Then
'do nothing
Else
wks.ResetAllPageBreaks
End If
Next wks

For Each hPB In mstrWks.HPageBreaks
MsgBox hPB.Location.Address
For Each wks In ActiveWorkbook.Worksheets
If wks.Name = mstrWks.Name Then
'do nothing
Else
wks.HPageBreaks.Add _
before:=wks.Range(hPB.Location.Address)
End If
Next wks
Next hPB

For Each vPB In mstrWks.VPageBreaks
MsgBox vPB.Location.Address
For Each wks In ActiveWorkbook.Worksheets
If wks.Name = mstrWks.Name Then
'do nothing
Else
wks.VPageBreaks.Add _
before:=wks.Range(vPB.Location.Address)
End If
Next wks
Next vPB

End Sub
 
Top