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

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,775
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?

VBA Code:
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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,403
Office Version
  1. 2013
Platform
  1. Windows
Hi Dave
We did this once before on an older version....do you still have it ??
lr is quicker this way

VBA Code:
Function LastRow()
lr =Sheets("CSS_quote_sheet") .Range("A:H").Find("*", , xlValues, , xlRows, xlPrevious).Row
End Function
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,775
I looked though them but none of the versions seemed to have that ability, I am not sure if I even implemented it
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,403
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

:::
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,403
Office Version
  1. 2013
Platform
  1. Windows
Can you upload the lates version ?
Also, If you want to search your old threads.....click on the Big "D" in your logo and select "Find", you can then look at all your old posts !
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,775

ADVERTISEMENT

There is too much information I have added so I am not sure I can upload it for privacy reasons. There are references all through it to the organisation I work for and it would be a mammoth job to remove it all.

I know it would be easier if you could see it so I appologise that I can't upload it.
 
Last edited:

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,775
There are 3 different signatures and the lowest top position of the biggest image for the first page is 512.706909179688 before it gets partly pushed to the next page. Not sure if that helps.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,403
Office Version
  1. 2013
Platform
  1. Windows
Was this it....That was in V19.5
VBA Code:
Private Sub CommandButton1_Click()
'insert custom image code from michael
Dim fNameAndPath As Variant
Dim img As Picture, shp As Shape
Set shp = Worksheets("sheet1").Shapes("TextBox4")
fNameAndPath = Application.GetOpenFilename(Title:="Select Signature To Be Imported")
If fNameAndPath = False Then Exit Sub
 Dim h As Long, lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
Set img = Worksheets("sheet1").Pictures.Insert(fNameAndPath)
  For h = 1 To 100
    If Sheets("sheet1").Rows(h).PageBreak <> xlPageBreakNone Then
    If lr >= h - 5 Then 'this is 5 rows before the page break
    With img
       .Left = 0
       .Top = Rows(lr) + 150 + shp.Top + shp.Height
    End With
    Else
    With img
       .Left = 0
       .Top = shp.Top + shp.Height + 50
    End With
    End If
    End If
  Next h
End Sub
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,775
Thanks for that Michael, I don't know what happened but I looked at v19.5 and that code wasn't in there. I think from memory that something went wrong and I found a simpler way of doing it????

I no longer have textbox4. There is a line called "Divider", which I think is just a shape and notes are written in the cells below it. I need the signature to be several lines below "Divider" or the lowest note, whichever is lower. Notes can be written in columns up to H. Thank you.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,127
Messages
5,546,075
Members
410,726
Latest member
TheSardOz
Top