Excel VBA Selecting Range of Cells to bottom of Print Area Page

Posted on 2012-08-14
Last Modified: 2012-08-14
I am trying to create an invoice in Excel. I have a precreated template on a sheet. The macro cleans up the information from a report that is exported from another software. It always comes out formatted the same(column-wise). As it stands now, It creates the bottom section of the invoice, then selections the used range for the cells on the bottom half then puts borders around them. What I am unsure on how to do is select only the first page border each section, then move to the second page and paste the header of the invoice again on the second page, then border the sections of the data again. To start the macro, you run the Create Invoice macro. If you have any questions, please let me know.

Any help is much appreciated. I'm attaching the sheet with the VB code in it. Feel free to critique this also. :)
Question by:tekkieguru
    LVL 13

    Accepted Solution

    Well, I can answer half of your question.  To repeat you header, say rows 1:6, in page setup set the "repeat rows at top" to 1:6.  You may want to move the date down 1 row, or shrink the image and use rows 1:5.

    As for putting a border around the data thats on 1 page, even if it breaks onto a 2nd page, I'm not sure.  (Rewording if someone else has trouble understanding what you were asking for).
    LVL 13

    Expert Comment

    Found the answer to the other half.  Conditional format.  Currently, I'm seeing 46 rows on a page, with 6rows as header (so 40 rows of actual data_. So you want a bottom border on every data thats a multiple of 40 + 6, =MOD(ROW()-6,40)=0

    Same thing for the top border on the next line, =MOD(ROW()-5,40)=0

    Author Closing Comment

    That worked great for the repeating header. I actually looked  at that section, but wasnt quite sure how to set it up.

    As far as the page break borders. I used the below code. Since it was just needed on the bottom of the invoice data section, i figured out how to find the page breaks.

    Private Sub PageBreakBorders()
    Dim pb As HPageBreak
    For Each pb In ActiveSheet.HPageBreaks
        LastRow = pb.Location.Offset(-1, 0).Row
        Range("B" & LastRow, "H" & LastRow).Select
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With

    End Sub

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    The canonical version of this article is on my web site here: A companion presentation is available here:
    Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now