Labels(Sticky, not Names) in Excel

Bagsy Baker

New Member
Joined
Feb 17, 2002
Messages
41
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.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

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