| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 questions: if-else & sheet loop
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 ------------------- -- s80N ----------------------------------------------------------------------- s80NL's Profile: http://www.excelforum.com/member.php...fo&userid=3637 View this thread: http://www.excelforum.com/showthread.php?threadid=56158 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
2 questions: if-else & sheet loop
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
2 questions: if-else & sheet loop
1 down, 1 more to go :) Thanks for providing already one of the solutions! -- s80NL ------------------------------------------------------------------------ s80NL's Profile: http://www.excelforum.com/member.php...o&userid=36374 View this thread: http://www.excelforum.com/showthread...hreadid=561587 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
2 questions: if-else & sheet loop
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 wrote: 1 down, 1 more to go :) Thanks for providing already one of the solutions! -- s80NL ------------------------------------------------------------------------ s80NL's Profile: http://www.excelforum.com/member.php...o&userid=36374 View this thread: http://www.excelforum.com/showthread...hreadid=561587 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
2 questions: if-else & sheet loop
Thnx a million! That works just fine so far, I will test it a little bit more but it looks very promissing  -- s80NL ------------------------------------------------------------------------ s80NL's Profile: http://www.excelforum.com/member.php...o&userid=36374 View this thread: http://www.excelforum.com/showthread...hreadid=561587 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sheet format questions -- Excel 2007 | New Users to Excel | |||
| two questions, Yes/No form control, and how did they create this adjustable area on the sheet... | Excel Discussion (Misc queries) | |||
| 2 Small VBA Questions; Text To Columns and Naming First Sheet | Excel Discussion (Misc queries) | |||
| Questions on copying from one sheet to the other and coping every other cell. | Excel Discussion (Misc queries) | |||
| 2 questions, copying data from sheet to sheet and assigning macro | Excel Worksheet Functions | |||
Linear Mode
