2 questions: if-else & sheet loop

S

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
 
D

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
 
S

s80NL

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top