Code to copy named ranges to PPT, where named ranges are variable

Question

I have a working code that will copy named ranges from a specific sheet on excel and then paste them to a specific sheet on powerpoint. However, it is not working exactly how I need it to.

The main issue is that I could have between 1 and 20 named ranges, based on the data in my workbook, so if I list all of the named ranges, then I get an error on the lines where these named ranges do not exist.

The code I am using is:

Sub copytablestoppt()
    Dim powerpointapp As Object
    Set powerpointapp = CreateObject("powerpoint.application")

    Dim destinationPPT As String
    destinationPPT = ("J:xxx\CPA1.ppt")

    On Error GoTo ERR_PPOPEN
    Dim mypresentation As Object
    Set mypresentation = powerpointapp.Presentations.Open(destinationPPT)
    On Error GoTo 0

    Application.ScreenUpdating = False

    PasteToSlide mypresentation.Slides(3), Worksheets("Start").Range("DataP")
    PasteToSlide mypresentation.Slides(52), Worksheets("Start").Range("IMEI")
    PasteToSlide mypresentation.Slides(4), Worksheets("Top Cell IDs & Top Contacts").Range("TopCellIDs1")

    powerpointapp.Visible = True
    powerpointapp.Activate

    Application.CutCopyMode = False

ERR_PPOPEN:
    Application.ScreenUpdating = True
    If Err.Number <> 0 Then
        MsgBox "Failed to open " & destinationPPT, vbCritical
    End If
End Sub


Private Sub PasteToSlide(mySlide As Object, rng As Range)
    rng.Copy
    mySlide.Shapes.PasteSpecial DataType:=2

    Dim myShape As Object
    Set myShape = mySlide.Shapes(mySlide.Shapes.Count)

    myShape.Left = 278
    myShape.Top = 175

End Sub

Does anyone have any ideas how to work around the error when my range does not exist?

There are several places in your code where you could usefully use a Try function. https://rubberduckvba.wordpress.com/2019/05/09/pattern-tryparse/ — freeflow, Dec 23 '22 at 10:27

score 1 · Answer 1 · answered Dec 23 '22 at 09:32

Check if the named range exists before copying. If it exists, then only copy it.

Dim rng As Range

On Error Resume Next
Set rng = Worksheets("Start").Range("DataP")
On Error GoTo 0

If Not rng Is Nothing Then
    PasteToSlide mypresentation.Slides(3), Worksheets("Start").Range("DataP")
End If

Important Note: If you are planning to use the above approach then always ensure that you add this line from 2nd named range onwards.

Set rng = Nothing '<~~~ 

On Error Resume Next
Set rng = Worksheets("Start").Range("IMEI")
On Error GoTo 0

Code to copy named ranges to PPT, where named ranges are variable

1 Answers1