0
Sub ChartExporter()

Dim PowerPointApp As Object
Dim myPresentation As Object
Dim mySlide As Object
Dim myShape As Object

Dim i As Integer
  
    If PowerPointApp Is Nothing Then _
    Set PowerPointApp = CreateObject(class:="PowerPoint.Application")
    
    On Error GoTo 0
    
    Application.ScreenUpdating = False
    
    Set myPresentation = PowerPointApp.Presentations.Add
    
For i = 1 To ThisWorkbook.Worksheets.Count
    
    Set mySlide = myPresentation.Slides.Add(1, 11) '11 = ppLayoutTitleOnly
    
    Worksheets(i).ChartObjects.Select 'THIS IS HIGHLIGHTED 
    
    Selection.Copy
    
    mySlide.Shapes.Paste
    
    Set myShape = mySlide.Shapes(mySlide.Shapes.Count)
    
    myShape.Left = 200
    myShape.Top = 200
    
    PowerPointApp.Visible = True
    PowerPointApp.Activate
    
    Application.CutCopyMode = False

Next


End Sub

VBA debugger points to the line containing Worksheets(i).Chartobjects.Select is highlighted. However, activesheet does seem to work.

My logic is that if i=1 then select chart objects, and copy those into the newly inserted slide, and then insert new slide again with worksheet(2) and so on...

Basically, I am trying to copy and paste every chart from every worksheet into every new slide.

iTOOK Urzjob
  • 31
  • 6
  • 1
    `Worksheets(i).ChartObjects(1).Copy` And if there are multiple chartobjects then llop through them using `Worksheets(i).ChartObjects(j).Copy` – Siddharth Rout Feb 07 '21 at 07:33
  • Thanks it worked. I wanted to ask another foolish question: is there difference between worksheets.chartobjects.select --> selection.copy, AND worksheets.chartobjects.copy (outcome wise)? Why does chartobjects.copy work seemlessly whilst selection.copy method doesn't copy properly? – iTOOK Urzjob Feb 09 '21 at 10:22
  • Using `Selection` is dicey. Better to work with objects.. You may want to see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Siddharth Rout Feb 09 '21 at 13:26
  • Thanks. I figured selection works with range objects not with other type objects. Cheers for clarification! – iTOOK Urzjob Feb 09 '21 at 22:27

0 Answers0