PDA

View Full Version : Solved: Formatting Macro Problem



gabethegrape
03-19-2009, 04:31 PM
I have a macro that formats the page the way I want it (please see attachment), but if I run it again on the same sheet it gives me an error message "Subscript out of Range". How can I override this error?

mdmackillop
03-20-2009, 12:46 AM
Problem appears to be if you have no page breaks, so near the end of your code

If Not ActiveSheet.VPageBreaks.Count = 0 Then _
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
If Not ActiveSheet.HPageBreaks.Count = 0 Then _
ActiveSheet.HPageBreaks(1).DragOff Direction:=xlDown, RegionIndex:=1

Simon Lloyd
03-20-2009, 01:23 AM
Also your code has too many "Selects" like:Rows(1:1).Select
Rows(1:1).Copy
Rows(2:2).Select
ActiveSheet.PasteWhen you can do this:Rows(1:1).Copy Destination:=Rows(2:2)Select can slow code down, you also make a lot of selects for no reason like:Range("K:H").Select
Range("H:J").Select
Range("C1").Selectso really all you wanted to get to there would have beenRange("C1"), these aren't snippets out of your code but just to give you an idea where you can tidy up to make it easier to understand.....also take a look at where you are formatting the font or cell, there are some portions of that code that you don't need so you can get rid of them.

gabethegrape
03-24-2009, 03:44 PM
Many thanks for your help. All the suggestions cleaned up my code very well.