How to get a shape by its name through a variable

mfp

New Member
Joined
Sep 16, 2020
Messages
1
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Good afternoon,

I am trying to create a macro, which copies the shapes that I've created on my worksheet and pastes them on a specific presentation. I see my idea very clear, but I can't get it working...

All my shapes are named through typing the name at the up-left corner (as you could do to name a cell normally). All these names are listed on a specific range, and my idea is using a variable that gets that name (identifying which shape has to get) so my macro knows which shape I am referring to and then copy it, I'have been trying a lot of things but I am not capable of define which type of variable I need to use; If I type the name of the shape between quotation marks, the macro does its thing but not by a variable

Thank you for your time

Dim vName$
vName$ = .Cells(rng.Row, 6).Value
HERE IS THE PROBLEM ---> Worksheets(vSheet$).ShapesObjects(vName$).Copy


The entire code

Rich (BB code):
Sub Export()

'-----------------------------


Dim ppt_app As New PowerPoint.Application
Dim pre As PowerPoint.Presentation
Dim slde As PowerPoint.Slide
Dim shp As PowerPoint.Shape
Dim wb As Workbook
Dim rng As Range

Dim vSheet$
Dim vRange$
Dim vType$
Dim vName$
Dim vSize As Double
Dim vWidth As Double
Dim vHeight As Double
Dim vTop As Double
Dim vLeft As Double
Dim vSlide_No As Long
Dim expRng As Range

Dim adminSh As Worksheet
Dim cofigRng As Range
Dim xlfile$
Dim pptfile$


Application.DisplayAlerts = False

Set adminSh = ThisWorkbook.Sheets("Macro")
Set cofigRng = adminSh.Range("Rng_sheets")

'xlfile = adminSh.[excelPth]
pptfile = adminSh.[pptPth]
     
Set wb = ThisWorkbook
Set pre = ppt_app.Presentations.Open(pptfile)


For Each rng In cofigRng
  
   With adminSh
      vSheet$ = .Cells(rng.Row, 4).Value
      vType$ = .Cells(rng.Row, 5).Value
      'vRange$ = .Cells(rng.Row, 6).Value
      vName$ = .Cells(rng.Row, 6).Value
      vWidth = .Cells(rng.Row, 7).Value
      vHeight = .Cells(rng.Row, 8).Value
      vTop = .Cells(rng.Row, 9).Value
      vLeft = .Cells(rng.Row, 10).Value
      vSize = .Cells(rng.Row, 11).Value
      vSlide_No = .Cells(rng.Row, 12).Value
   End With
  
  
   '----------------- EXPORT TO PPT
  
            wb.Activate
            Sheets(vSheet$).Activate
           
    If vType$ = "Chart" Then
   
            Worksheets(vSheet$).ChartObjects(vName$).Copy
           
            Set slde = pre.Slides(vSlide_No)
           
            With slde.Shapes.PasteSpecial(ppPasteDefault)
              
               .Top = vTop
               .Left = vLeft
               .Width = vWidth
               .Height = vHeight
              
            End With
    Else
            Worksheets(vSheet$).ShapesObjects(vName$).Copy
           
            Set slde = pre.Slides(vSlide_No)
           
            With slde.Shapes.PasteSpecial(ppPasteDefault)
               .Top = vTop
               .Left = vLeft
               .Width = vWidth
               .Height = vHeight
              
            End With
  
   End If
  
Set shp = Nothing
            Set slde = Nothing
            Set expRng = Nothing
  
   Application.CutCopyMode = False
   Set expRng = Nothing
  
Next rng

pre.Save
pre.Close

Set pre = Nothing
Set ppt_app = Nothing

wb.Save


Application.DisplayAlerts = True

End Sub
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,455
You should be referring to the Shapes collection, not ShapesObjects...

VBA Code:
Worksheets(vSheet$).Shapes(vName$).Copy
 

Watch MrExcel Video

Forum statistics

Threads
1,127,205
Messages
5,623,363
Members
415,969
Latest member
Rey99

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
Top