.HPagebreaks.Add method causing runtime error 1004

[vba317]

I am running an Access 97 program. To create Horizontal page breaks on an excel spreadsheet. My original code caused a runtime error -2147417851. I went through the archives and I found an interesting solution but now I am getting a runtime error 1004. Any help is appreciated.
Currently iRow = 45 so I know the error is not because of a null.
Tom
Original code

If (iPageBreakCount = 4) Then
                  With goXL.Sheets("AR Report-Aging")
                   .Cells((iRow - 1), 1).Select  
                     .HPageBreaks.Add Before:=ActiveCell                                 
                   End With
                   iPageBreakCount = 0

Current Code

If (iPageBreakCount = 4) Then
                  With goXL.Sheets("AR Report-Aging")
                  ActiveWindow.SelectedSheets.HPageBreaks.Add _
                  Before:=Cells(iRow - 1)

                  End With
                   iPageBreakCount = 0

 

[SkipVought]

You must [highlight]reference[/highlight] the Excel Cell object to the sheet, but I'm not sure that you can mass add a page break like that, as the .Cells just references ONE sheet...

If (iPageBreakCount = 4) Then
                  With goXL.Sheets("AR Report-Aging")
                  ActiveWindow.SelectedSheets.HPageBreaks.Add _
                  Before:=[highlight].[/highlight]Cells(iRow - 1)

                  End With
                   iPageBreakCount = 0

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[vba317]

The code is cycling through 1 sheet at a time. Should I change the .cells to something else?

 

[PHV]

If (iPageBreakCount = 4) Then
  With goXL.Sheets("AR Report-Aging")
    [!]goXL.[/!]ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=[!].[/!]Cells(iRow - 1)
  End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

Sorry but this code causes an error 438 Object doesnt support this property or method.

 If (iPageBreakCount = 2) Then
                        'With goXL.Sheets("Executive Summary")
                        With goXL.ActiveWindow
                        .SelectedSheets.HPageBreaks.Add Before:=.Cell(iRow - 1) ' Added HPage break 7/23/2013 TD
                            '.Cells(iRow, 1).Select 'Commented out 7/23/2013 TD
                            
                            '.HPageBreaks.Add Before:=ActiveCell
                        End With 'Commented out 7/23/2013 TD
                        iPageBreakCount = 0

End If

 

[PHV]

Anyway, why not simply this ?

If iPageBreakCount = 2 Then
  With goXL.Sheets("AR Report-Aging")
    .HPageBreaks.Add Before:=.Cells(iRow - 1)
  End With
  iPageBreakCount = 0

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886