Labels(Sticky, not Names) in Excel

[Bagsy Baker]

I created a set of spreadsheets to handle production scheduling and paperwork for an old employer a few years ago. I was able to make everything work within Excel, except for labels. For these I created an Access database and used import functions to create a label report. Now I want to go back and create the labels within Excel so that they don't have to open two programs. I got the layout to work, as well as the left and right column, but the page layout goes funny. I've tried to use the following code to set where I want the page breaks, and have set the margins the same as the the label sheet, but Excel keeps sticking in extra page breaks.

Sub ResizeSheet()



Dim lastrw
Dim countpage

Worksheets("MF-H").Activate

Range("a65000").Activate
lastrw = ActiveCell.End(xlUp).Row + 7 'set # to last actual label bottom
countpage = Int((lastrw) / 100) + 1 'tells how many full sheets are required


ActiveSheet.ResetAllPageBreaks
For y = 1 To countpage
Range("a" & y * 100 + 9).Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
Next y
Range("t1").Select
ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=ActiveCell
ActiveWindow.SelectedSheets.PrintPreview
End Sub


I tried to get around it by using this too;
.FitToPagesWide = 1
.FitToPagesTall = countpage

but still no go.

Any ideas? I know Access is the better program to use, but the end user does not like or feel comfortable enough with Access, so I would like to have everything in one program. Any ideas or thoughts would be appreciated.

 

[NateO]

Excel seems to ignore hard page breaks when you use the pages wide by pages tall nomenclature if it is not consistent with Excel's 'zooming' of the document. It will only recognize your page breaks if you the ratio setup is consistent with your page breaks... E.g.,

With ActiveSheet.PageSetup
.Zoom = 95
End With
End Sub

vba for "adjust to x% of normal size" in the page setup...

Cheers, Nate
This message was edited by NateO on 2002-02-25 14:53