VBA to paste onto a new page

dpaton05

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

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

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
UNTESTED, but I'm sure you can modify to suit
VBA Code:
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
 
Upvote 0
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.
VBA Code:
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.
VBA Code:
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?
 
Upvote 0
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
VBA Code:
.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?
 
Upvote 0
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.
 
Upvote 0
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. :unsure:
 
Upvote 0

Forum statistics

Threads
1,223,980
Messages
6,175,766
Members
452,668
Latest member
mrider123

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