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

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,352
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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
 
Upvote 0
I have all the older versions but there is about 140 of them.
 
Upvote 0
I looked though them but none of the versions seemed to have that ability, I am not sure if I even implemented it
 
Upvote 0
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 !
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,757
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top