Solved: Vertical pagebreak and dragging off

[Knud]

Hi all,

I'm having difficulties with setting up the vertical pagebreak in VBA.

My challenge is, I need to make a vertical pagebreak between columns AI and AJ and three horisonal pagebreaks (before row 56, 101 and 174).
The horisontal pagebreaks work, but I can't get the vertical pagebreak to work properly. Excel puts in an extra vertical pagebreak in the middle. Therefore I thought I had to add a line with some .dragoff. However this didn't do the trick.

The page needs to be A4-size, so I can't just change the paper size.

Below you see my current code.

---
ActiveSheet.ResetAllPageBreaks 'to make sure there are no prior preset pagebreaks
Set R = ActiveSheet.UsedRange '

ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=R.Cells(56, 1)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=R.Cells(101, 1)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=R.Cells(174, 1)
ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=R.Cells(1, 36)
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
---

When I run the VBA code I get the error "Application-defined or object-defined error" and the last line is marked, and I can't figure out to solve this :(

I hope somebody can help me with this, I'm sure it's just a small thing.

Thanks in advance.

Kind regards,
Knud

[GTO]

Hi all,

I'm having difficulties with setting up the vertical pagebreak in VBA.

Below you see my current code...

---
ActiveSheet.ResetAllPageBreaks 'to make sure there are no prior preset pagebreaks
Set R = ActiveSheet.UsedRange '

ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=R.Cells(56, 1)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=R.Cells(101, 1)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=R.Cells(174, 1)
ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=R.Cells(1, 36)
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1

When I run the VBA code I get the error "Application-defined or object-defined error" and the last line is marked, and I can't figure out to solve this :(

I hope somebody can help me with this, I'm sure it's just a small thing.

Thanks in advance.

Kind regards,
Knud

Greetings Knud,

When I do my best to duplicate your error, I can get past the error after having removed an Excel initiated H break or two. Then, at least given the info you included, I end up with something that's going to print at about 28% size. I'm betting this is not your intent.

"to make sure there are no prior preset pagebreaks"

ResetAllPageBreaks does not erase or delete all the pagebreaks on the sheet, it resets them to where Excel thinks they should be.

Thus - its a bit hard to tell whether your are getting rid of the only V break or leaving another one.

I think it would be easier if you could attach a sheet showing where you want any/all breaks.

Also - Please use the green/white VBA button. After clicking it, paste your code and it will neatly format it. When including code, I try and stay at/below 85 columns (characters) per line, so it doesn't 'push out' to the side.

Hope this helps,

Mark

[Knud]

Greetings Mark (and everybody else who would like to help :-) ),

I have attached a sheet that shows you my task. I'm using Excel 2007 (forgot to mention that the first time), and I want my macro to setup the sheet so that it is divided into 3 pages (horisontal pagebreak before row 56, before row 101 and before row 174 and the width of the pages should be fra column A to AI (AI included)) before either sending the sheet or printing the sheet (I don't want to print 8 pages, when it should be 3 :-) ).
As you can see in the attached excel sheet, I have three other macros on the sheet, which is why I want to setup the sheet to 3 pages tall (the information will still be readible).

The issue is, I can't get the vertical pagebreak to work properly. Excel puts in a vertical pagebreak between columns P og Q. I have tried the dragoff-function, but I can't get it to work. I constantly get the error mentioned in my previous post. I think the print size should be around 53 % (to get columns A to AI on an A4 sized paper, 53 % I think is the correct size).

I hope this helps you to help me : pray2:,

Knud

[GTO]

Hi Knud,

I am off to bed (it's past 0200 hrs here), so won't be able to see your next post til later. Unfortunately, I can only access up to Excel 2003 (currently actually working in 2000), so cannot take a look.

My bet is that someone will already have helped by the time I get to re-check, but in the meantime, try this:

First, make sure you have the Print Area set. To do this, select File|Page Setup on the MenuBar. Click the tab that says "Sheet" (presuming this hasn't changed...). Now you'll see the print area section and the little button to reduce the form and select/drag to encompass the entire range (A1 to AI174). After clicking <OK> until back to your sheet...

Turn the macro recorder on.

On the menu bar, again select File|PageSetup, this time select the "Page" tab. In the scaling section, select the "Fit To" radio button (option button), and select "1" page wide by "3" pages tall. <OK> button til back to your sheet, and turn the macro recorder off.

In reviewing the code that the recorder has written, you will be able to see how everything was set. While this will not finish solving the problem, you'll be able to take this code (modified, as the code recorded is hardly ever ideal) and combine it with code to move and/or add and delete the horizontal page breaks as desired. But hey(!) we already got rid of that stinky vertical break :-)

In closing, if you don't already have this all the way solved by this evening (your evening time), you could do a saveas on the workbook, choosing to save as 2003 format, and post it so anyone not having 2007 can take a look.

Hope that all made sense and sorry I couldn't be of more help thus far.

Have a great day,

Mark

[Knud]

Hi Mark,

I tried the recording of a new macro, after I had setup it up to 1 page wide and 3 tall, but the code was useless in my opinion (see below).

So unfortunately, I still haven't fixed the problem :dunno

With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.708661417322835)
.RightMargin = Application.InchesToPoints(0.708661417322835)
.TopMargin = Application.InchesToPoints(0.748031496062992)
.BottomMargin = Application.InchesToPoints(0.748031496062992)
.HeaderMargin = Application.InchesToPoints(0.31496062992126)
.FooterMargin = Application.InchesToPoints(0.31496062992126)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 3
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With

I guess I'll just have to wait til you are awake again, Mark, unless there is somebody else who can help me.

Knud

[GTO]

Hi Knud,

Insomnia strikes again...ergh. Anyways, how about saving a copy in 2003 format and post it. I'll take a look.

Mark

[Knud]

Hey Champ,

I actually had made a copy in Excel 2003, but didn't post it for some strange reason :banghead:

But here it is.

Knud

[Knud]

Greetings all,

I finally solved it. I don't know why, but it now works, see code below:

ActiveWindow.View = xlPageBreakPreview 'This was the line that was missing
Set R = ActiveSheet.UsedRange

ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=R.Cells(56, 1)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=R.Cells(101, 1)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=R.Cells(174, 1)
ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=R.Cells(1, 36)

ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1 'Original

Mark, thanks for your help.

Knud