Excel Page Breaks in Loop problem

[TTPicasso]

I am trying to print a range selected by the user from 2 combo boxes, The first to select a sheet, the second to select a number of weeks to print.

The sheet, and range work fine but excel ignores the page breaks pre-set, I then coded in the page breaks but recieve an error that I cannot seem to sort (new to VB!!) however on 1 machine in the office it works fine even though the excel version is identical but the 2 other machines i have tried it on fail with the same error.

The error is...
Run-time error '1004':
Application-defined or object-defined error

And is caused by this line within a loop.
Set ActiveSheet.HPageBreaks(ActiveSheet.HPageBreaks.Count).Location = ActiveCell

Here is the complete code

Sub PrintSchedule()

Dim intRng As Integer 'Final Row Number to Print
Dim intFitTo As Integer 'Pages to Print/fit to
Dim intCounter As Integer 'Loop counter

Range("D1&quot.Activate
intRng = ActiveCell.Value * 84 + 6
intFitTo = ActiveCell.Value
intCounter = 1

Range("C1&quot.Activate
Worksheets("Komori" & Str(ActiveCell.Value + 3) & " Unit&quot.Select

Range("A6:H" & Trim(Str(intRng))).Select
ActiveSheet.PageSetup.PrintArea = "$A$7:$H$" & Trim(Str(intRng))

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$6"
.PrintTitleColumns = ""
'.LeftHeader = ""
'.CenterHeader = ""
'.RightHeader = ""
'.LeftFooter = ""
.CenterFooter = "SFS / QF.084"
'.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.28)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.17)
.BottomMargin = Application.InchesToPoints(0.4)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
'.PrintHeadings = False
'.PrintGridlines = False
'.PrintComments = xlPrintNoComments
'.PrintQuality = 300
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
'.Order = xlDownThenOver
.BlackAndWhite = True
'.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = intFitTo
End With

ActiveSheet.ResetAllPageBreaks

Do While intCounter < intFitTo
ActiveSheet.HPageBreaks.Add ActiveSheet.Range(&quot;A&quot; + LTrim(Str((intCounter * 84) + 7)))
Set ActiveSheet.HPageBreaks(ActiveSheet.HPageBreaks.Count).Location = ActiveCell
intCounter = intCounter + 1
Loop

Range(&quot;A6:H&quot; & Trim(Str(intRng))).Select
Selection.PrintOut Copies:=1, Collate:=True

ActiveSheet.PageSetup.PrintArea = &quot;&quot;
Range(&quot;C7&quot.Select
Sheets(&quot;Print&quot.Select
Range(&quot;A1&quot.Select
End Sub


ANY IDEAS PLEASE!!!

 

[ide]

delete the set word from the

Set ActiveSheet.HPageBreaks(ActiveSheet.HPageBreaks.Count).Location = ActiveCell

the .Location is a property, not a cell typed object

ide

 

[TTPicasso]

Many thanks to IDE, that cured the problem!!

 

[TTPicasso]

Almost!

Excel ignores the page breaks set at the end of the loop and prints past them and I have no idea how to cure this problem! intCounter counts from 1 upwards, intFitto is the number of pages to print (weeks) and the &quot;+ 6&quot; is the header lines.

The second ActiveSheet syntax was added to try to force Excel to accept the page break and i presumed would not be required, but alas! no joy.

Code is...........

ActiveSheet.ResetAllPageBreaks

Do While intCounter < intFitTo
ActiveSheet.HPageBreaks.Add ActiveSheet.Range(&quot;A&quot; + LTrim(Str((intCounter * 84) + 6)))

ActiveSheet.HPageBreaks(intCounter).Location = Range(&quot;$A$&quot; + LTrim(Str((intCounter * 84) + 6)))
intCounter = intCounter + 1
Loop

Hoping for an answer!!!