Pushing an image to the following page if it is over the page break

[dpaton05]

I have a procedure that pastes in a signature from another sheet, just below the bottom of a quote. Is there a way to push it to the next page if the signature ends up being half on one page and half on the next?

Function LastRow()

    With Sheets("CSS_quote_sheet")
        LastRow = .Range("A:H").Find(What:="*", _
            After:=.Range("A1"), _
            Lookat:=xlPart, _
            LookIn:=xlFormulas, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious, _
            MatchCase:=False).Row
    End With
End Function
Sub cmdSig()
    Sheets("Sheet2").Shapes("ImgT").Copy
    ActiveSheet.Paste Destination:=ActiveSheet.Cells(1, 1)
    Selection.Top = Cells(LastRow, 1).Top + "100"
End Sub

 

[dpaton05]

I run the sub called push and it almost does what I need it to do. I just need the signature also to appear below any notes written under the required elements under the table. I have tried to modify the code but it will not appear below any notes written in a similar fashion to appearing below the table.

Function LastRow()
'Dim LastRow As Long
    With Sheets("CSS_quote_sheet")
        LastRow = .Range("A:H").Find(What:="*", _
            After:=.Range("A1"), _
            Lookat:=xlPart, _
            LookIn:=xlFormulas, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious, _
            MatchCase:=False).Row
    End With
End Function

Sub push()


Dim a As Double, aa As Double, aaa As Double
Application.ScreenUpdating = False
    With Sheets("Sheet2")
        .Shapes("ImgG").Duplicate.Name = "Signature"
        .Shapes("Signature").Cut
    End With
        Sheets("CSS_quote_sheet").Cells(43, 1).PasteSpecial
        Sheets("CSS_quote_sheet").Shapes(Selection.Name).Name = "Signature"
        a = Sheets("CSS_quote_sheet").Cells(Rows.Count, 1).End(xlUp).Offset(1).Top + 140
        aa = Sheets("CSS_quote_sheet").Shapes("Signature").Height
        aaa = Rows(Sheets("CSS_quote_sheet").HPageBreaks(1).Location.Row).Top + 1
    With Sheets("CSS_quote_sheet").Shapes("Signature")
        .Left = ActiveSheet.Range("A1").Left
        .Top = IIf(a + aa > aaa, aaa, a)
        .Placement = 1
    End With
Application.ScreenUpdating = True
End Sub

 

[dpaton05]

I forgot to put in the code I am trying to run. Here it is:

        Function LastRow()
'Dim LastRow As Long
With Sheets("CSS_quote_sheet")
LastRow = .Range("A:H").Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End With
End Function

Sub push()


Dim a As Double, aa As Double, aaa As Double
Application.ScreenUpdating = False
With Sheets("Sheet2")
.Shapes("ImgG").Duplicate.Name = "Signature"
.Shapes("Signature").Cut
End With
Sheets("CSS_quote_sheet").Cells(43, 1).PasteSpecial
Sheets("CSS_quote_sheet").Shapes(Selection.Name).Name = "Signature"
a = Sheets("CSS_quote_sheet").Cells(LastRow, 1).End(xlUp).Offset(1).Top + 140
aa = Sheets("CSS_quote_sheet").Shapes("Signature").Height
aaa = Rows(Sheets("CSS_quote_sheet").HPageBreaks(1).Location.Row).Top + 1
With Sheets("CSS_quote_sheet").Shapes("Signature")
.Left = ActiveSheet.Range("A1").Left
.Top = IIf(a + aa > aaa, aaa, a)
.Placement = 1
End With
Application.ScreenUpdating = True
End Sub

This code will not place the signature below any notes that are written in columns A:H.

 

[dpaton05]

Thanks for your code, I modified it a bit more and got it to work with running the sub push.

Function LastRow()
'Dim LastRow As Long
    With Sheets("CSS_quote_sheet")
        LastRow = .Range("A:H").Find(What:="*", _
            After:=.Range("A1"), _
            Lookat:=xlPart, _
            LookIn:=xlFormulas, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious, _
            MatchCase:=False).Row
    End With
End Function

Sub push()


Dim a As Double, aa As Double, aaa As Double
Application.ScreenUpdating = False
    With Sheets("Sheet2")
        .Shapes("ImgG").Duplicate.Name = "Signature"
        .Shapes("Signature").Cut
    End With
        Sheets("CSS_quote_sheet").Cells(43, 1).PasteSpecial
        Sheets("CSS_quote_sheet").Shapes(Selection.Name).Name = "Signature"
        a = Sheets("CSS_quote_sheet").Cells(LastRow, 1).Top + 140
        aa = Sheets("CSS_quote_sheet").Shapes("Signature").Height
        aaa = Rows(Sheets("CSS_quote_sheet").HPageBreaks(1).Location.Row).Top + 1
    With Sheets("CSS_quote_sheet").Shapes("Signature")
        .Left = ActiveSheet.Range("A1").Left
        .Top = IIf(a + aa > aaa, aaa, a)
        .Placement = 1
    End With
Application.ScreenUpdating = True
End Sub

 

[dpaton05]

Actually, if the notes go onto the next page, using this code, the signature will appear in the top left corner of the second page, regardless of where the notes are.

 

[dpaton05]

Actually, if the notes go onto the next page, using this code, the signature will appear in the top left corner of the second page, regardless of where the notes are.

I forgot to mention, the distance it appears under the table is the same distance it needs to appear under the notes, but using this code, the signature appears the set distance under the table but ignores the notes. The signature is pasted over the notes where it should be under them.

 

[dpaton05]

Are you still there jolivanes? It isn't working

 

[jolivanes]

Is this a continuation from here?

vba to find the last row in a table in excel

I have a table that can have rows added to it by pressing a button and I need to know the vba code to determine the last row in the table. The button is pressed when the table is finished and no more lines need adding. Could someone help me as I am still learning how to code? Thanks, Dave

www.mrexcel.com

If you work with a table, which I don't use because I don't have any need for it, it changes all your finding last rows etc.

 

[dpaton05]

Since posted that thread about 2 years ago, I can't remember what it was regarding but I know this thread wouldn't be a continuation from it.

 

[dpaton05]

I am sure I had it working at some point. I do not know what I have done as now the signature is being pasted over any text that appears as notes under the table. The signature appears in the right spot if there are no notes but when there are notes, I need it to appear under the notes.

When I execute cmdPush, the signature appears as shown if there are no notes.

Function LastRow()
'Dim LastRow As Long
    With Sheets("CSS_quote_sheet")
        LastRow = .Range("A:H").Find(What:="*", _
            After:=.Range("A1"), _
            Lookat:=xlPart, _
            LookIn:=xlFormulas, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious, _
            MatchCase:=False).Row
    End With
End Function

Sub cmdPush()
Dim a As Double, aa As Double, aaa As Double
Application.ScreenUpdating = False
    With Sheets("Sheet2")
        .Shapes("ImgG").Duplicate.Name = "Signature"
        .Shapes("Signature").Cut
    End With
        Sheets("CSS_quote_sheet").Cells(43, 1).PasteSpecial
        Sheets("CSS_quote_sheet").Shapes(Selection.Name).Name = "Signature"
        a = Sheets("CSS_quote_sheet").Cells(LastRow, 1).End(xlUp).Offset(1).Top
        aa = Sheets("CSS_quote_sheet").Shapes("Signature").Height
        aaa = Rows(Sheets("CSS_quote_sheet").HPageBreaks(1).Location.Row).Top + 1
    With Sheets("CSS_quote_sheet").Shapes("Signature")
        .Left = ActiveSheet.Range("A1").Left
        .Top = IIf(a + aa > aaa, aaa, a + 140)
        .Placement = 1
    End With
Application.ScreenUpdating = True
End Sub

If there are notes, the signature is pasted in over the top of them like in this image

I need the signature to appear in the position it is in if there are no notes but if there are notes, I need it below any notes. At the moment, it will not appear under any notes that are written.

Another issue is that if there has been enough rows added to the table or enough notes written under the table to push the signature half way over the page, I want it pushed to the next page.
This is what it looks like at the moment if there is enough rows added but not enough to fully move it to the next page.

I have been getting some help with the coding but I am getting very confused and not sure what to do.

Please help someone,
Thanks

 

[jolivanes]

You showed 3 pictures here. For each of these, what is the lastrow figure and what is the the "HPageBreaks(1).Location.Row).Top" number?
In the 1st picture, the "lastrow" row should be 24, in the 2nd it should be 30 and in the 3rd it should be 33.
It looks like, on picture #3 that the page break should be at the top of row 37.
If you have all the figures for the above, it should be simple arithmetic to see where it goes wrong.
It also looks like your signature picture is 4 rows high.
If your last row (= below data, notes etc) + signature height > 36, your print area needs to be 2 pages