B
BillzyBop
I am attempting to write a procedure that moves the Hpagebreaks on a worksheet so that all of the data from one section
is on the same page. I have written a procedure that does just that, but it does not work when I call it from another
procedure. It always fails with a 1004 error. If I call the procedure manually from the immediate window it runs perfect
every time.
Here is the code:
Public Sub PageBreaks(strWsName As String)
Dim phHoriz As HPageBreak
Dim rngPageBreak As Range
Dim nochanges As Boolean
Dim shtTimeCard As Worksheet
Set shtTimeCard = Sheets(strWsName)
nochanges = False
Do Until nochanges
For Each phHoriz In shtTimeCard.HPageBreaks
nochanges = True
Set rngPageBreak = phHoriz.Location
If rngPageBreak.Value <> "" Then
nochanges = False
Do Until rngPageBreak.Value = ""
Set rngPageBreak = shtTimeCard.Cells(rngPageBreak.Row - 1, 1)
Loop
Set phHoriz.Location = rngPageBreak
End If
Next phHoriz
Loop
End Sub
The error always occurs at the line "Set phHoriz.Location = rngPageBreak". I have tried various ways around it, and am
perplexed as to the behavior I am seeing. I mean, if there is an error, why does it alway work when run from the
immediate window, but never when called from any other procedure? If anyone has any ideas, I would really appreciate the
feedback.
Thanks,
Bill
is on the same page. I have written a procedure that does just that, but it does not work when I call it from another
procedure. It always fails with a 1004 error. If I call the procedure manually from the immediate window it runs perfect
every time.
Here is the code:
Public Sub PageBreaks(strWsName As String)
Dim phHoriz As HPageBreak
Dim rngPageBreak As Range
Dim nochanges As Boolean
Dim shtTimeCard As Worksheet
Set shtTimeCard = Sheets(strWsName)
nochanges = False
Do Until nochanges
For Each phHoriz In shtTimeCard.HPageBreaks
nochanges = True
Set rngPageBreak = phHoriz.Location
If rngPageBreak.Value <> "" Then
nochanges = False
Do Until rngPageBreak.Value = ""
Set rngPageBreak = shtTimeCard.Cells(rngPageBreak.Row - 1, 1)
Loop
Set phHoriz.Location = rngPageBreak
End If
Next phHoriz
Loop
End Sub
The error always occurs at the line "Set phHoriz.Location = rngPageBreak". I have tried various ways around it, and am
perplexed as to the behavior I am seeing. I mean, if there is an error, why does it alway work when run from the
immediate window, but never when called from any other procedure? If anyone has any ideas, I would really appreciate the
feedback.
Thanks,
Bill