Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

insert line at each page break

Hi

I have the code below - which inserts a line at each page break only if I run it from page break view - but in normal view it only inserts it on the first page?!

Code:
 
ActiveWorkbook.Names.Add Name:="Print_View", RefersToR1C1:=Range("B27", Range("F65536").End(xlUp))
    ActiveSheet.PageSetup.PrintArea = "Print_View"
    Range("Print_View").Select
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$5:$26"
        .PrintTitleColumns = ""
    End With
    .
    .
    .
    Dim i As Long
     'Erase old borders
    For Each c In Range(Range("B:B").Find("DATE", lookat:=xlWhole).Offset(10), Range("B65536").End(xlUp))
        If c.Borders(xlEdgeBottom).LineStyle = xlContinuous Then
            c.EntireRow.Borders(xlEdgeBottom).LineStyle = xlNone
        End If
    Next c
     'Write borders at the bottom of each page
    For i = 1 To ActiveSheet.HPageBreaks.Count
        With Range(ActiveSheet.HPageBreaks(i).Location.Address).Offset(-1).EntireRow.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlThick
            .ColorIndex = xlAutomatic
        End With
    Next
     'Write border at the very bottom
    Range("B65536").End(xlUp).EntireRow.Borders(xlEdgeBottom).LineStyle = xlContinuous
    'Application.ScreenUpdating = True
    ActiveSheet.PrintPreview


Post your answer or comment

comments powered by Disqus
Hi.
I want to quickly add an end line at each page break on a large worksheet.
(eg for printing purposes )
Thank you

Could someone please tell me how to get rid of <null> at each page break. This happens after data is imported to make a report.

Does anyone know whether it's possible (and if so, how) to insert a title row
at the beginning of a page break when printing? I want to have one specific
row at the top of each printed page and the header function isn't meeting my
need.

I need to insert a line at each change in "property Number" in a very long
list of data. Can I set it up to automatically insert a line at each change
(like subtotal, but without the subtotal data)?

Hello friends,

I have a problem with table border in excel. Table containing vertical lines and outer table border. When I try to print it, I am placing horizontal line manually in each page break. It is time consuming. How can I solve this problem. Please help. Your suggestion will be highly appreciated. Thank you.

Sample fil is also attached.

Hi All,

My workbook is essentially a list of suppliers, I have various information on the service they have provided dates, invoice number and amount owing. By first selecting all rows and organising by the Suppliers name I get a neat list which I can apply the subtotal function to. This accurately splits out my suppliers showing a subtotal under each for the amount I owe them and putting in a page break between each supplier.

In the past I paid them all by cheque so I simply printed this all out and gave them a copy as their remittance. I now pay them by bacs and they receive their remittance by email. Most want/require their remittance in the form of a pdf. The problem is that when I print to PDF it saves the entire workbook as one document.

I need a macro to save each supplier to a different pdf document with the supplier name. I have had a go at this but am relatively useless with VBA.

Do
Selection.Copy
Range("AL2").Select
ActiveSheet.Paste
ActiveSheet.Range("$V$3:$V$63356").AutoFilter Field:=1, Criteria1:=Range("AL2")
Dim FP As String, FN As String
FP = "W:Simon"
FN = Range("AL2").Value
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FP & FN & Format(Date, " dd-mm-yy"), Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
ActiveSheet.ShowAllData
Columns("AK:AK").Select
Selection.Find(What:=Range("AL2").Value, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Selection.Offset(1, 0).Select
Loop Until IsEmpty(Range("AL2"))
Range("B3").Select
End Sub

Any help at all would be very much appreciated and save me a lot of time each month. Thanks in advance

Hi
I work with tables that are varying in length between 10 and 100 pages.

I am trying to figure how to automatically create a bottom border to the table at each page break. It is time consuming to do it manually ech time a row is removed or added within the table.

To further complicate this, I use the top 5 rows as Print Titles. These rows are of varying heights.

Thanks

I have a file with results from various departments/divisions in the company.
Is there any way to have each page break create a new worksheet? I have to
email each dept/div to the proper manager. There a many, many depts/div and
it takes a long time to do this each week.

thanks,
--
Tere for Stan

Hi All,

How can I draw a line on each page break? I do not want to display it in the screen, however the line will be printed while user prints the spreadsheet.

I wont be able to use the custom footer in page setup, as I need to provide the page number.

Thank you!

Hi

This is my first post here. I'm in the middle of programing a complicated 180-page movie budget. I was wondering how can I make the first line on each new page read out the column names AND make it a permanent and unmovable line. In other words, how can I make it like a heading... except that it also adjusts back and forth if I move a column with?

Just so you know, the first line on each page should read above each column: Category, Item, Amount, Unit, etc.

EXAMPLE HERE:

Hello,

I need to create a macro that create a worksheet for each page break from the original worksheet and copy the data from the original worksheet.
It's more complcate for me.....
thank you

Hello,
I am trying to make a custom header that will show up at the top of
each page. I have a long list (500 rows) of information with 7 columns,
and I have the headers in the cells at the top of the list, but I'd like
to have the column headers show up when I print it out without having to
go through and insert them at each page break (this might mess with my
formulas anyway). The custom header option only has left,right, and
center, and it looks like it will take a while to space the headers out
to match up correctly. Is there a better way to do this? Thanks in
advance.

--
GeyserPeak
------------------------------------------------------------------------
GeyserPeak's Profile: http://www.excelforum.com/member.php...o&userid=20746
View this thread: http://www.excelforum.com/showthread...hreadid=385572

Hello,
I am trying to make a custom header that will show up at the top of each page. I have a long list (500 rows) of information with 7 columns, and I have the headers in the cells at the top of the list, but I'd like to have the column headers show up when I print it out without having to go through and insert them at each page break (this might mess with my formulas anyway). The custom header option only has left,right, and center, and it looks like it will take a while to space the headers out to match up correctly. Is there a better way to do this? Thanks in advance.

Hello all ,
I found this code from Ron De Bruin to insert page breaks depending on the number of rows.
Works great , but i need to adjust it for my purposes.
I'm helping someone else on this forum for this problem.
In a sheet i define the rows(1:15) to be repeated at the top for printing.
I'd like to insert page breaks from row 16 every RW rows ( RW = define rows in the code)
Has someone have a solution for this.
Many thanks

Sub Insert_PageBreaks()
    Dim Lastrow As Long
    Dim Row_Index As Long
    Dim RW As Long

    'How many rows do you want between each page break
    RW = 20
    
    With ActiveSheet
        'Remove all PageBreaks
        .ResetAllPageBreaks
        
        'Search for the last row with data in Column A
        Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
        
        For Row_Index = RW + 1 To Lastrow Step RW
            .HPageBreaks.Add Before:=.Cells(Row_Index, 1)
        Next
    End With
End Sub


I have a macro that inserts lines and copy a header when ever the value in B
Changes.
I want to make it a bit easier the 26 lines must be inserted and range
B2:K25 coppied at every page break.

Thanks

Public Sub Deilv2()
Dim LastRow As Long
Dim row_index As Long
Dim rng As Range
Set rng = Range("B2:K25")
Application.ScreenUpdating = False
LastRow = ActiveSheet.Cells(Rows.Count, "b").End(xlUp).Row
For row_index = LastRow - 1 To 26 Step -1
If Cells(row_index, "B").Value _
Cells(row_index + 1, "B").Value Then
Cells(row_index + 1, "B").Resize(26).EntireRow. _
insert Shift:=xlDown
rng.Copy Destination:=Cells(row_index + 1, "B").Offset(2)
End If
Next
Application.ScreenUpdating = True
End Sub

I have an EXCEL 2003 file containing about 7000 addresses like this:
FIRST LAST NUMBER STREET
Mary Smith 123 Main
Tom Jones 789 Maple St.
Fred Clark 456 Main St.

I want to insert page breaks so that when I print it, each street will be
together on the same page. That is, Smith and Clark will be on the same page
because they both live on Main St. and Jones will be another page because he
lives on Maple St.

I would sort the worksheet by Street, then by number. There are more than
one hundred street names. I would rather not have to manually insert each
page break at a new street name. Is there a way to get EXCEL to
automatically make page breaks each time the street name changes? I will
appreciate advice.

I have an Excel spreadsheet that is longer than one page but when there is a
page break, is sometimes splits lines where I don't want them split. How can
I keep 2 or more lines together even if they are at the end of a page. I want
both lines to go to the second page, rather than one line at the bottom of
the first page and the second line by itself at the top of the second page.
I've tried adjusting margins and inserting page breaks, but this is
cumbersome when the pages keep changing. (The page breaks are moved when a
new line is inserted above it).
Is there a way to keeps lines together in Excel. I'm using the new Beta
version of Excel 7 but if it's possible to keep lines together in older
versions, it should work here also.
Any help would be greatly appreciated. Thanks.

I have a macro that inserts lines and copy a header when ever the value in B
Changes.
I want to make it a bit easier the 26 lines must be inserted and range
B2:K25 coppied at every page break.

Thanks

Public Sub Deilv2()
Dim LastRow As Long
Dim row_index As Long
Dim rng As Range
Set rng = Range("B2:K25")
Application.ScreenUpdating = False
LastRow = ActiveSheet.Cells(Rows.Count, "b").End(xlUp).Row
For row_index = LastRow - 1 To 26 Step -1
If Cells(row_index, "B").Value <> _
Cells(row_index + 1, "B").Value Then
Cells(row_index + 1, "B").Resize(26).EntireRow. _
insert Shift:=xlDown
rng.Copy Destination:=Cells(row_index + 1, "B").Offset(2)
End If
Next
Application.ScreenUpdating = True
End Sub

Dear all,

Hope you all are fine. I am seeking for a help from you all. May be it is very simple. But, am not able to crack it down. I would like to put a line at the end of each page in XL work sheet. More clear, I need not want to use the border option or drawing line maually at each page. My search, is If I draw a line in the end of a first page of a worksheet, it should automatically reflect in all other remaining pages.

Can you suggest a simple method to do so. I feel if it is in the top of a page we can use page set up and go for rows to repeat at top. Whereas to get a line at bottom am feeling bit tricky.

Expect your valued suggestion.

with best regards

Vinu

Dear all,

Hope you all are fine. I am seeking for a help from you all. May be it is very simple. But, am not able to crack it down. I would like to put a line at the end of each page in XL work sheet. More clear, I need not want to use the border option or drawing line maually at each page. My search, is If I draw a line in the end of a first page of a worksheet, it should automatically reflect in all other remaining pages.

Can you suggest a simple method to do so. I feel if it is in the top of a page we can use page set up and go for rows to repeat at top. Whereas to get a line at bottom am feeling bit tricky.

Expect your valued suggestion.

with best regards

Vinu

vinumv

#2 Today, 02:10 PM
cycling-rod is Offline:
Registered User Join Date: Aug 2004
Posts: 188

One simple way is to fill colour a row with black and then close it up with the cursor to make a thick line. Copy and paste this line down the sheet wherever and above the dashed lines marking the page length on the spreadsheet appear.
Another simple way is to use: =REPT("_",1000) in A54 (if row 54 marks the last row in a Print Preview). The under score "_" is the character to form the line, and the 1000 is the number of times to repeat "_" (adjust the 1000 for length as required). Format the row to thicken the line and close up the row height to something like 7.50. Then copy and paste down the sheet on every 54th row.
Hopefully, someone will produce a way to put a line at the foot of each page using code.

cycling-rod
View Public Profile

#3 Today, 03:36 PM
olasa is Offline:
Registered User Join Date: Dec 2004
Location: Sweden
Posts: 283

...if so, then I would add: =IF(MOD(ROW(),54)=0,REPT("_",100),"")
ROW() will return the row number that the formula is located in.
MOD(..,54) will calculate the remainder (54/54=0, 541/54=1, ...)

But I would prefer to use Conditional Formatting with the Condition1: Formula Is:
=MOD(ROW(),54)=0 and format the Boarder line (see encl. picture).

Ola Sandström

Note:
All your printing area must be marked when the Condition is entered.
Attached Images Clipboard01.jpg (84.6 KB, 2 views)

olasa

#4 Today, 06:39 PM
vinumv is Online:
Registered User Join Date: Mar 2005
Posts: 4

Thanks a lot for both of urs effort and contribution.

What I found is, the formulas are helpful when the row height is constant through out the spreadsheet. Whereas in my cases, the row height varies in each page according to the matter what I fill in the cell. So, when it counts the multiples of 54, the second line goes in the row 108, which apparently am getting in the begining of third page whereas I would like to get the line at the end of Page-2.

As such, it will be really appreciated if you can come up with the idea of putting line in the footer or something like that.

with regards

Vinu

vinumv
View Public Profile

#5 Today, 07:28 PM
olasa is Offline:
Registered User Join Date: Dec 2004
Location: Sweden
Posts: 283

Dealing with variable row heights are quite a different matter (=cell(..) can calculate column width but not height).
Try to post your question in the programming forum and see if they can come up a solution.

Good luck
Ola

since this is the suggestion from Ola am putting my prob in the programming forum to get a solution.

with regards
Vinu

I am trying to achieve a row insert based on matching criteria. I need to check a column of text values, and each time the text value changes, copy cells (a1:c1) and insert copied selection to the row before text change.

I am only concerned with the text in the first column. For example, I have a column with sometext in each row, when the row changes to somenewtext, I want to copy the header information and insert into row before the text changed.

Header1|Header2|Header3|
sometext
sometext
sometext
somenewtext

Searching the forums, I found conditional page breaks http://www.ozgrid.com/Excel/excel-co...age-breaks.htm and tried to adapt the code, but have been unsuccessful in getting it to work for my needs. I have tried the following, but cant figure out how to insert the rows in the correct place. Here is what I tried.

	VB:
	
 cln() 
    Dim myRange As Range 
    Dim rngCell As Range 
     
    With Worksheets("pendingRpt") 
        Set myRange = .Range(.Range("A1"), .Range("A65536").End(xlUp)) 
        For Each rngCell In myRange 
            If rngCell.Text  rngCell.Offset(1, 0).Text Then 
                rngCell.Select 
                With Selection.Interior 
                    Range("A1:C1").Select 
                    Selection.Copy 
                    Selection.Insert shift:=xlDown 
                End With 
            End If 
        Next 
    End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any and all help is appreciated.

So basically I have a report that is generated that is broken down into 4 or 5 headers. What I want is basically a way to manually add page breaks for 2 conditions:
*That no section starts at the end of one page and ends on the next; I want to just insert a page break before the section so it is all on the next page.
* That if the first section is more than a page long, that it inserts a new header saying 'section 1 continued..' basically.

I've been able to code it and it functions how I want. However, it takes about 20-30 seconds on a dual-core computer with 2gb of memory to insert these page breaks, so I thought I'd get some expert advice on where the slow-downs are and how I could go about tightening up the code to get it to run faster. (this subprocedure is #15 of 15 when generating the report, and the rest take between 2 and 5 seconds combined).

	VB:
	
 Quote_Page_Breaks() 
    Dim financials_start_row As Integer, financials_start_page As Integer, financials_end_row As Integer, financials_end_page
As Integer 
    Dim recommended_start_row As Integer, recommended_start_page As Integer, recommended_end_row As Integer,
recommended_end_page As Integer 
    Dim standard_end_row As Integer, standard_end_page As Integer 
    Dim i As Integer, page_break() As Integer 
    Dim recommended As Boolean 
    On Error Resume Next 
     'get the row numbers of the start of each section
    If Quote_CE_P_Cab Then standard_end_row = Sheets("Quotation").Range("Q_Options").Row - 1 Else standard_end_row =
Sheets("Quotation").Range("Q_Base_Unit").Row - 1 
    financials_start_row = Sheets("Quotation").Range("Q_Financials").Row 
    If Quote_Options_Recommendations = True Then 
        recommended_start_row = Sheets("Quotation").Range("Q_Recommended").Row 
        financials_end_row = recommended_start_row - 2 
        recommended_end_row = ActiveCell.SpecialCells(xlLastCell).End(xlToLeft).End(xlUp).Row 
    Else 
        financials_end_row = ActiveCell.SpecialCells(xlLastCell).End(xlToLeft).End(xlUp).Row 
        recommended_start_row = 0 
        recommended_end_row = 0 
    End If 
     'get the row numbers of each page break
    Redim page_break(1) 
    For i = 1 To ActiveSheet.UsedRange.Rows.Count 
        If Rows(i).PageBreak = xlManual Then 
            Redim Preserve page_break(UBound(page_break) + 1) 
            page_break(UBound(page_break)) = i 
        ElseIf Rows(i).PageBreak = xlAutomatic Then 
            Redim Preserve page_break(UBound(page_break) + 1) 
            page_break(UBound(page_break)) = i 
        End If 
    Next 
    Redim Preserve page_break(UBound(page_break) + 1) 
    page_break(UBound(page_break)) = ActiveCell.SpecialCells(xlLastCell).Row 
     'get the page numbers of each section
    For i = 1 To UBound(page_break) - 1 
        If (standard_end_row >= page_break(i) And standard_end_row < page_break(i + 1)) Then standard_end_page = i 
        If (financials_start_row >= page_break(i) And financials_start_row < page_break(i + 1)) Then financials_start_page =
i 
        If (financials_end_row >= page_break(i) And financials_end_row < page_break(i + 1)) Then financials_end_page = i 
        If Quote_Options_Recommendations = True Then 
            If (recommended_start_row > page_break(i) And recommended_start_row < page_break(i + 1)) Then
recommended_start_page = i 
            If (recommended_end_row >= page_break(i) And recommended_end_row < page_break(i + 1)) Then recommended_end_page =
i 
        End If 
    Next 
    If financials_start_page = 0 Then financials_start_page = UBound(page_break) - 1 
    If financials_end_page = 0 Then financials_end_page = UBound(page_break) - 1 
    If recommended_start_page = 0 Then recommended_start_page = UBound(page_break) - 1 
    If recommended_end_page = 0 Then recommended_end_page = UBound(page_break) - 1 
     'check the values and add page breaks if needed
    If standard_end_page > 1 Then 
        Sheets("Quotation").Rows(page_break(2) & ":" & page_break(2)).Insert shift:=xlDown 
        Call Quote_Format_Border(Sheets("Quotation").Range("a1").Offset(page_break(2) - 1, 0).Address, "Standard
Specifications continued...") 
    End If 
    If financials_start_page  financials_end_page Then 
        Sheets("Quotation").Range("Q_Financials").Select 
        ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Selection 
    End If 
    If Quote_Options_Recommendations = True Then 
        If recommended_start_page  recommended_end_page Then 
            Sheets("Quotation").Range("Q_Recommended").Select 
            ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Selection 
        End If 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Notes:
The page is broken up into the following named sections:
*Q_Standard_Specifications
*Q_Options (not always present)
*Q_Base_Unit
*Q_Options_Chosen
*Q_Financials
*Q_Recommended (not always present)
These are all variable length depending on the data used to generate the report and the type of report generated (which is why 2 of them aren't always there).

That's all that I can think of. Let me know if I left out any explanations, and thanks a bunch for the help!

UPDATE:
Well I think I got it working well enough. I replaced the page_break lookup code with the following, and it works like a champ now:

	VB:
	
Names.Add "HPBreaks", "=GET.DOCUMENT(64)", False 
page_break = [HPBreaks] 'so using evaluate to fill a variant array
Redim Preserve page_break(UBound(page_break) + 2) 
For i = UBound(page_break) - 1 To 1 Step -1 
    page_break(i + 1) = page_break(i) 
Next 
page_break(1) = 0 
page_break(UBound(page_break)) = Range("A65536").End(xlUp).Row 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Hope this helps someone else maybe.

Is there any way to force a border to be printed around the printed area (like PowerPoint's print function, for example)? If not, I can obviously enclose the entire print are in a simple line border, but this doesn't show a border at page breaks.

If not, is there a way, or can anyone provide a macro or other way to do so, to draw a border at every hard page break (soft breaks are OK too, but not required). That is, to force a border line at the page break so the resulting format has a border around the printed information on each page.

Many thanks.

Question from a newbie here: I have a sheet with 46000 rows and I want to insert a blank row after every page break which in my case is every 37 rows. I have this code that I can't make function properly:


	VB:
	
 InsertRows() 
    Dim startRow As Range 
    Set startRow = Rows("37:37") 
    Application.ScreenUpdating = False 
    Do Until Application.CountA(startRow) = 0 
        startRow.Insert Shift:=xlDown 
        Set startRow = startRow.Offset(38, 0) 
    Loop 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Once that is okay I also want to add text to all these blank rows with a border outlining that row. The text is the same in each row.
Can anyone halp me out?

Modifying the header to suit my needs is not an option.


No luck finding an answer? You could always try Google.