2 questions: if-else & sheet loop

[s80NL]

Hi there,

Just started to create a set of macro's to generated a report from
messy input sheet. This is the first time I work with macro's so
still have a lot to learn

In one macro I want to loop through all (currently 12) available shee
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).DragOf
Direction:=xlToRight, RegionIndex:=1" optional?? Because on some sheet
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 particulai
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 Su

 

[Die_Another_Day]

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]

1 down, 1 more to go

Thanks for providing already one of the solutions!

 

[Die_Another_Day]

Well seeing as how no one else has helped I'll take a stab at it. Mind
you that this is definately not your best option.

On error resume next
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1

if err.number = 9 then
'Do what you want
err.clear
Else
err.raise
end if
on error goto 0

HTH

Die_Another_Day

 

[s80NL]

Thnx a million! That works just fine so far, I will test it a little bit
more but it looks very promissing