How to identify the last page break in a sheet

[dpaton05]

How can I find the position of the last page break in my sheet? There may be multiple pages and I want to know how to find the position of the first page break below the last row.

 

[Michael M]

I don't have Excel at the moment, but try....Step through with F8 to see what happens and where it may error !

Sub cmdPush(user As String)
Dim a As Double, aa As Double, aaa As Double, DividerBottom As Long
Dim NoPages As Long, ws As Worksheet, n As Long
Set ws = Sheets("CSS_quote_sheet")
'Finds the number of pages
n = ws.HPageBreaks.Count
NoPages = ((ActiveSheet.HPageBreaks.Count + 1) * (ActiveSheet.VPageBreaks.Count + 1)) / 2
Application.ScreenUpdating = False
    With Sheets("Sheet2")
        .Shapes(user).Duplicate.Name = "Signature"
        .Shapes("Signature").Cut
    End With
        ws.Cells(43, 1).PasteSpecial
        ws.Shapes(Selection.Name).Name = "Signature"
        a = ws.Cells(LastRow, 1).End(xlUp).Offset(1).Top + 140
        aa = ws.Shapes("Signature").Height
        aaa = ws.HPageBreaks(n).Location.Row - 1
        DividerBottom = ws.Shapes("Divider").BottomRightCell.Row
    With ws.Shapes("Signature")
        .Left = ActiveSheet.Range("A1").Left
        .Top = IIf(a + aa > aaa, aaa, a)
        .Placement = 1
    End With
Application.ScreenUpdating = True
End Sub

 

[dpaton05]

Is that code any different to mine, besides the variable names? Using my code, it doesn't error, just doesn't put the image in the right spot. Well, it does error on the NoSig sub but I have it commented out and will deal with it after I get this bit working.

 

[Michael M]

Is that code any different to mine,

Not really....did you try it ??

 

[dpaton05]

No I didn't as I thought it was the same

 

[Michael M]

Ummm, why would I post it if it was the same ??

 

[dpaton05]

Good point, I will try it when I am next at work on Friday.

 

[dpaton05]

My sheet which has 4 pages of rows to the table, so the last row of the table is in row 108. Below the table there is a textbox called divider. Below that, there is place to write notes. If no notes are written, I want the image to be placed 140 below divider. If there are notes, I want the image to be 140 below the bottom note but if the image is spread across 2 pages, I want it pushed to the second page.

I tried your code from post 11 on my spreadsheet and the image was placed with the top of the image inline with the top of row 10. This is a snapshot of the sheet

CSS_quoting_tool_33.37.xlsm
	A	B	C		E	F	G	H
9
10	Date	Service	Unit Price		Hours	Staff Req.	Kms Travelled	Price ex. GST
11	16/07/2020	Tutoring	$73.10		1	1		$73.10
CSS_quote_sheet

Cell Formulas
Range	Formula
C11	C11	=IF([@Service]="Activities",[@Activities],INDEX(Service_Types,MATCH([@Service],Sheet2!$A$5:$A$12,0),MATCH([@[Day rate]],Sheet2!$A$5:$E$5,0)))
H11	H11	=IF([@Service]="Activities",ROUNDDOWN([@Activities]+[@[Transport $]],2),IF([@Service]="Carer Respite",[@[Staff Req.]]*[@Rate],ROUNDDOWN(((IF(OR(ISBLANK(A11),ISBLANK(D11),ISBLANK(B11)),0,[@[Transport $]]+[@MaxPay]))*[@[Staff Req.]]),2)))

 

[Michael M]

I'm up in Narrabri atm so will be offline for a few days. ..but I think you may have to look for another way around it

 

[dpaton05]

Ok, I will try.

Thanks for letting me know

 

[dpaton05]

AAGGGHHH, this is doing my head in. I have had to revert to having the image split across 2 pages if it is positioned at that point instead of being pushed to the second page. I couldn't get it working.