Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I insert data into my spreadsheet without the page breaks. | Excel Worksheet Functions | |||
Excel: how to insert page breaks between all the rows at once? | Excel Discussion (Misc queries) | |||
How do I insert page breaks in .txt for use in excel? | Excel Discussion (Misc queries) | |||
Insert Multiple Page Breaks | Excel Worksheet Functions | |||
insert page breaks every 43 lines | Excel Programming |