Automate Page Breaks in Excel 2

[genuineblonde]

I have a worksheet with several columns of data, sorted by column A. Is there a way to insert a page break automatically whenever the value changes in column A?

 

[macropod]

Hi GB,

The following macro inserts a page break whenever the value in Column D (indicated by the '4' in Cells(i,4) is a '1'.

Sub PrintFormat()
Application.ScreenUpdating = False
For i = 2 To ActiveSheet.UsedRange.Rows.Count
If Cells(i, 4).Value = 1 Then
Cells(i, 1).PageBreak = xlPageBreakManual
End If
Next
Application.ScreenUpdating = True
End Sub

Similarly, the following macro inserts a page break whenever the value in Column D changes:


Sub PrintFormat()
Application.ScreenUpdating = False
For i = 2 To ActiveSheet.UsedRange.Rows.Count
If Cells(i, 4).Value = Cells((i - 1), 4).Value Then
Cells(i, 1).PageBreak = xlPageBreakManual
End If
Next
Application.ScreenUpdating = True
End Sub

Cheers

 

[genuineblonde]

Hi, I appreciate your help...however I am getting the following when I try to run the macro (2nd option).

Runtime error '1004'

Unable to set the pagebreak property of the range class.


The range I am breaking on is a person name.

 

[macropod]

Hi,

Sorry, the line:
Cells(i, 4).Value = Cells((i - 1), 4).Value
should have been:
Cells(i, 4).Value <> Cells((i - 1), 4).Value
Not that this change should have had anything to do with the error you're getting - works for me with both text and numbers.

Cheers
PS: Note too that the test is case-sensitive. To ignore case you could use:
UCase(Cells(i, 4).Value) <> UCase(Cells((i - 1), 4).Value)
and, if you're concerned that extra spaces might sometimes appear in the data and you want to ignore them, use:
Trim(UCase(Cells(i, 4).Value)) <> Trim(UCase(Cells((i - 1), 4).Value))

 

[misscrf]

Macro, this is excellent. It is just what I was looking for.

I am having one problem though, and I was hoping you or someone might be able to help me out.


among other things, I sort my data, once I clean up the sheet, then I attempt to implement this page break. It is breaking based on column B, which contains numbers.

Here is my code:

  Columns("B:B").Select
    Range("A1:J81").sort Key1:=Range("B2"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
      For i = 2 To ActiveSheet.UsedRange.Rows.Count
         If Cells(i, 2).Value <> Cells((i - 1), 2).Value Then
         Cells(i, 1).PageBreak = xlPageBreakManual
        End If

Here is what happens -

It sorts fine, then the break gives me the first row, which repeats on every page, by itself as the first page. I would like to stop that from happening.

Is there something wrong with my code?

Once I get this fixed, I have one more thing, and then I think my macro will be set.

Thanks to anyone that can help! and thanks, Macro for posting this solution!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[genuineblonde]

Could it be that you have the first line identified to repeat in the Page Setup?

 

[misscrf]

I do, but why is that a problem? I think I see why, but I need to figure out how to make it ignore this repeating of the first row. Can we do that?

sorry I have not replied; getting over pnuemonia.

Thanks.

misscrf

It is never too late to become what you could have been ~ George Eliot