VBA to paste onto a new page

[dpaton05]

I have some code to paste a range below the current range. I want it to find the bottom of the document and then create a new page after it to paste the range onto. There might be an image put in at the bottom of the document . At the moment, the code I have to insert the range is:

        With WS
            .Range("A8:V32").Copy .Range("A" & Rows.Count).End(xlUp).Offset(10, 0) 
        End With

This code works if I just want a fixed distance below the bottom of the bottom range, but this can cause the image to be split over multiple pages and I need the image to be on one page and have each range start on a new page.

Can someone help me with this code please?

 

[Michael M]

UNTESTED, but I'm sure you can modify to suit

With WS
            .Range("A8:V32").Copy .Range("A" & Rows.Count).End(xlUp).Offset(10, 0)
            .Range("A" & Rows.Count).End(xlUp).Offset(10, 0).PageBreak = xlPageBreakManual
End With

 

[dpaton05]

Thank you Michael!

 

[dpaton05]

Just realised that it only partially works. There are two different types of work that the copied rows calculate prices for. When I select one type of work. the rows copy in the right place and everything is fine. If I select the second type of work. I get the ambiguous error Object doesn't support this property or method. This gives me no indication as to what might be causing it.

This is the code that is run when I get the error.

Sub AddRows()
Dim WS As Worksheet
Set WS = ThisWorkbook.Worksheets("ACA_Quoting")
    Call Move_Shape
        With WS
            .Range("A" & Rows.Count).End(xlUp).Offset(10, 0).PageBreak = xlPageBreakManual
            .Range("A8:V32").Copy .Range("A" & Rows.Count).End(xlUp).Offset(10, 0)         'Pastes a copy of the table below current table between the bottom of the table and the totals
        End With
End Sub

The buttons are copied so I thought the problem is in the move_shape procedure.

Sub Move_Shape()
    Dim Total As Range
    Dim WS As Worksheet
    Dim Sh As Shape, NewShape As Shape
    Dim TTop As Long
    Dim TLeft As Long
    Dim txtMainExists As Boolean

    Set WS = ThisWorkbook.Worksheets("ACA_Quoting")
    
    For Each Sh In WS.Shapes
        Debug.Print Sh.Type
        Select Case Sh.Name
        Case "cmdAddRatio", "cmdCustomSign", "cmdGsign", "cmdNoSign", "cmdSaveToPdf"
            Sh.IncrementTop 472                       'reduce increment for test purposes 505
        Case "txtMain"                                'name your first textbox, the one you want to move,  to something unique. I used "txtMain"
            txtMainExists = True
            TTop = Sh.Top                             'record position
            TLeft = Sh.Left
            Sh.IncrementTop 472                        'reduce increment for test purposes 505505
            Sh.Copy                                   'make a copy
            WS.Paste
            Set NewShape = WS.Shapes(WS.Shapes.Count)    'pasted textbox is the last shape
            With NewShape
                .Top = TTop                           'move the copy to the previous position of txtMain
                .Left = TLeft
                .OLEFormat.Object.Object.Text = .Name & "    (a copy of txtMain)"
            End With
        End Select
    Next Sh
    If Not txtMainExists Then
        MsgBox "txtMain is missing!", vbCritical
    End If
End Sub

I tried to step through it and when it gets to the line of code WS.Paste, I get the error "Can't enter break mode at this time"


I am starting to understand VBA a little more but I am not sure what to do here. Could you help me please Michael?

 

[dpaton05]

Not sure if you need to know this bit, the two types of work previously mentioned are

• Stand up Work
• Sleepover Work

They are selected from a drop down menu in cell B11

If I comment out the line WS.Paste in the move_shape sub, I get the error Object doesn't support this property or method but this time, there is a debug option. The following line of code is highlighted

.OLEFormat.Object.Object.Text = .Name & "    (a copy of txtMain)"

I tried to comment out that line of code as well and I stopped getting the error. What do those two lines of code do? Do I really need them?

 

[dpaton05]

Actually, I do need them as when I run it with them commented out, txtMain is not moved to below the last range that has been copied.

I also forgot to mention in my last post, I have the problems when Stand up Work is selected, everything works fine with Sleepover Work.

 

[dpaton05]

This is really strange. I removed the comments and ran it again with those two lines of code and it ran fine. No idea what why that happened.