Macro to insert page breaks when values in 1 or more columns changes
 

I have spreadshete that uses MS Query to load data. The
data needs page breaks when values change in some key
columns. How do I write a macro that scans the key columns
and inserts a horizontal page break when the values change.

Macro to insert page breaks when values in 1 or more columns changes
 

Do you mean vertical pagebreak? If you really want horizontal, then what
row would need to be checked for differences - you description doesn't match
horizontal. Writing a macro requires specific information. What are you
key columns - what condition requires a pagebreak, where is you data located
and so forth.

--
Regards,
Tom Ogilvy

Michael G wrote in message
...
I have spreadshete that uses MS Query to load data. The
data needs page breaks when values change in some key
columns. How do I write a macro that scans the key columns
and inserts a horizontal page break when the values change.

Macro to insert page breaks when values in 1 or more columns changes
 

The spreadsheet is sorted by values in columns A, B & C.
When any of the values in these columns change a page
break needs to be inserted. I'm assuming I can select the
range $A,$B,$C , search the range and when there is a
change insert a vertical page break.

-----Original Message-----
Do you mean vertical pagebreak? If you really want
horizontal, then what
row would need to be checked for differences - you
description doesn't match
horizontal. Writing a macro requires specific
information. What are you
key columns - what condition requires a pagebreak, where
is you data located
and so forth.

--
Regards,
Tom Ogilvy

Michael G wrote in
message
...
I have spreadshete that uses MS Query to load data. The
data needs page breaks when values change in some key
columns. How do I write a macro that scans the key
columns
and inserts a horizontal page break when the values
change.


.

Macro to insert page breaks when values in 1 or more columns changes
 

Thanks for your code. I am new in Macro. We need to insert
a page number after the break line? Can you tell me how to
do so?



-----Original Message-----
Michael,

You were correct - you want horizontal pagebreaks - guess
I got disoriented.
<g

Sub AddBreaks()
Dim rng As Range
Dim cell As Range
Set rng = Range("A1").CurrentRegion.Columns(1).Cells
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
For Each cell In rng
If cell.Value < cell.Offset(1, 0).Value _
Or cell.Offset(0, 1).Value _
< cell.Offset(1, 1).Value _
Or cell.Offset(0, 2).Value < _
cell.Offset(1, 2).Value Then
ActiveSheet.HPageBreaks.Add _
Befo=cell.Offset(1, 0)
End If
Next
End Sub

--
Regards,
Tom Ogilvy


Michael G wrote in
message
...
The spreadsheet is sorted by values in columns A, B & C.
When any of the values in these columns change a page
break needs to be inserted. I'm assuming I can select
the
range $A,$B,$C , search the range and when there is a
change insert a vertical page break.

-----Original Message-----
Do you mean vertical pagebreak? If you really want
horizontal, then what
row would need to be checked for differences - you
description doesn't match
horizontal. Writing a macro requires specific
information. What are you
key columns - what condition requires a pagebreak,
where
is you data located
and so forth.

--
Regards,
Tom Ogilvy

Michael G wrote in
message
...
I have spreadshete that uses MS Query to load data.
The
data needs page breaks when values change in some key
columns. How do I write a macro that scans the key
columns
and inserts a horizontal page break when the values
change.


.



.

Macro to insert page breaks when values in 1 or more columns changes
 

Go into View=Header and Footer and create a custom footer or header that
contains the page number. (Excel will generate the actual page number - you
just specify in the Header or footer that you want a page number using the #
icon.

--
Regards,
Tom Ogilvy

"Lin" wrote in message
...
Thanks for your code. I am new in Macro. We need to insert
a page number after the break line? Can you tell me how to
do so?



-----Original Message-----
Michael,

You were correct - you want horizontal pagebreaks - guess
I got disoriented.
<g

Sub AddBreaks()
Dim rng As Range
Dim cell As Range
Set rng = Range("A1").CurrentRegion.Columns(1).Cells
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
For Each cell In rng
If cell.Value < cell.Offset(1, 0).Value _
Or cell.Offset(0, 1).Value _
< cell.Offset(1, 1).Value _
Or cell.Offset(0, 2).Value < _
cell.Offset(1, 2).Value Then
ActiveSheet.HPageBreaks.Add _
Befo=cell.Offset(1, 0)
End If
Next
End Sub

--
Regards,
Tom Ogilvy


Michael G wrote in
message
...
The spreadsheet is sorted by values in columns A, B & C.
When any of the values in these columns change a page
break needs to be inserted. I'm assuming I can select
the
range $A,$B,$C , search the range and when there is a
change insert a vertical page break.

-----Original Message-----
Do you mean vertical pagebreak? If you really want
horizontal, then what
row would need to be checked for differences - you
description doesn't match
horizontal. Writing a macro requires specific
information. What are you
key columns - what condition requires a pagebreak,
where
is you data located
and so forth.

--
Regards,
Tom Ogilvy

Michael G wrote in
message
...
I have spreadshete that uses MS Query to load data.
The
data needs page breaks when values change in some key
columns. How do I write a macro that scans the key
columns
and inserts a horizontal page break when the values
change.


.



.

Macro to insert page breaks when values in 1 or more columns changes
 

See you had some important information in the subject. Best to put all the
information in the body of the email

you can go into Data=subtotals and have it do this for you.

--
Regards,
Tom Ogilvy

Tom Ogilvy wrote in message
...
Go into View=Header and Footer and create a custom footer or header that
contains the page number. (Excel will generate the actual page number -
you
just specify in the Header or footer that you want a page number using the
#
icon.

--
Regards,
Tom Ogilvy

"Lin" wrote in message
...
Thanks for your code. I am new in Macro. We need to insert
a page number after the break line? Can you tell me how to
do so?



-----Original Message-----
Michael,

You were correct - you want horizontal pagebreaks - guess
I got disoriented.
<g

Sub AddBreaks()
Dim rng As Range
Dim cell As Range
Set rng = Range("A1").CurrentRegion.Columns(1).Cells
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
For Each cell In rng
If cell.Value < cell.Offset(1, 0).Value _
Or cell.Offset(0, 1).Value _
< cell.Offset(1, 1).Value _
Or cell.Offset(0, 2).Value < _
cell.Offset(1, 2).Value Then
ActiveSheet.HPageBreaks.Add _
Befo=cell.Offset(1, 0)
End If
Next
End Sub

--
Regards,
Tom Ogilvy


Michael G wrote in
message
...
The spreadsheet is sorted by values in columns A, B & C.
When any of the values in these columns change a page
break needs to be inserted. I'm assuming I can select
the
range $A,$B,$C , search the range and when there is a
change insert a vertical page break.

-----Original Message-----
Do you mean vertical pagebreak? If you really want
horizontal, then what
row would need to be checked for differences - you
description doesn't match
horizontal. Writing a macro requires specific
information. What are you
key columns - what condition requires a pagebreak,
where
is you data located
and so forth.

--
Regards,
Tom Ogilvy

Michael G wrote in
message
...
I have spreadshete that uses MS Query to load data.
The
data needs page breaks when values change in some key
columns. How do I write a macro that scans the key
columns
and inserts a horizontal page break when the values
change.


.



.