I can help you with question 1 but we'll have to wait for someone more
intelligent for question 2
Sub RefreshPageBreaks()
Dim WS as Worksheet
For Each WS in ActiveWorkbook.Worksheets
WS.Select
ActiveWindow.View = xlPageBreakPreview
Range("A1").Select
ActiveSheet.PageSetup.PrintArea = ""
ActiveSheet.ResetAllPageBreaks
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
ActiveWindow.View = xlNormalView
Range("A1").Select
Next
HTH
Die_Another_Day
s80NL wrote:
Hi there,
Just started to create a set of macro's to generated a report from a
messy input sheet. This is the first time I work with macro's so I
still have a lot to learn :)
In one macro I want to loop through all (currently 12) available sheet
to "refresh" the page break settings for those sheets.
1) How can I transfor this into a loop construction?
2) How can I make "ActiveSheet.VPageBreaks(1).DragOff
Direction:=xlToRight, RegionIndex:=1" optional?? Because on some sheets
I get the following error -Run-time error "9": Subscript out of range-.
I suspect this is caused by the fact that the data on that particulair
sheet can be printed @ 100% on a single page...
Code:
--------------------
Sub RefreshPageBreaks()
' This is, of course, only part of the code I have for the 12 tables...
Sheets("PO").Select
ActiveWindow.View = xlPageBreakPreview
Range("A1").Select
ActiveSheet.PageSetup.PrintArea = ""
ActiveSheet.ResetAllPageBreaks
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
ActiveWindow.View = xlNormalView
Range("A1").Select
Sheets("CC").Select
ActiveWindow.View = xlPageBreakPreview
Range("A1").Select
ActiveSheet.PageSetup.PrintArea = ""
ActiveSheet.ResetAllPageBreaks
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
ActiveWindow.View = xlNormalView
Range("A1").Select
End Sub
--------------------
--
s80NL
------------------------------------------------------------------------
s80NL's Profile: http://www.excelforum.com/member.php...o&userid=36374
View this thread: http://www.excelforum.com/showthread...hreadid=561587