tekkieguru
asked on
Excel VBA Selecting Range of Cells to bottom of Print Area Page
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. :)
CustInv---Example---EE.xlsm
Any help is much appreciated. I'm attaching the sheet with the VB code in it. Feel free to critique this also. :)
CustInv---Example---EE.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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(xlEdgeBo ttom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Next
End Sub
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(xlEdgeBo
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Next
End Sub
Same thing for the top border on the next line, =MOD(ROW()-5,40)=0