Managing Page Breaks with VBA

yussi1870

Board Regular
Joined
Mar 18, 2002
Messages
139
Office Version
  1. 365
Platform
  1. Windows
I've created a Macro which runs through an old worksheet, copies most of the data and pastes into a new worksheet where it is then reformatted. The last thing I need to do is set the page breaks so that the worksheet is only one page wide (it can be an indefinite number of pages long). I have used the following code to achieve this:

With ActiveSheet.PageSetup
.AdjustTo = False
.FitToPagesWide = 1
.FitToPagesTall = False
.FitTo = True
End With

This works at setting the Fit To section of the Page set up to 1 by blank but the radio button stays in the Adjust To part. It's not that the worksheet is just too wide for this to work since it is only three columns and when I manually set it to one page by blank it works fine. For some reason it just insists on leaving the page break after the second column. I've even tried the following code which doesn't seem to have any effect at all on the worksheet:

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

ActiveSheet.VPageBreaks(1).Location = ActiveSheet.Range("c5")

All of this is simply an attempt to make one page wide which includes only columns A, B, and C.

Any ideas?
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Try the following code:
With ActiveSheet.PageSetup
.PrintArea = "$A:$C"
.FitToPagesWide = 1
.FitToPagesTall = False
End With

The code sets the print area to columns A:C and set the page wide to 1.
 
Upvote 0
Thanks guys! Adding both the Print Area and setting the Zoom to False finally set my page breaks right.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top