copy specific excel charts to a specific powerpoint slides

**coolin** · 08-17-2009, 07:42 AM

Hello everyone,

I have an excel sheet with 20 charts, these charts are updated from time to time..and have to paste them in a powerpoint presentation.
I need a macro that copy every chart to a specific slide in my powerpoint template.
For example, Chart(1) must be paste in slide2, chart(2) must be paste in slide6 and so on...

I found on another forum a macro code that copies only one chart to one specific slide, I need to modified to copy one by one my charts to the specified slides in powerpoint..

Sub test_paste2()
Dim Sh As Shape
Dim PP As PowerPoint.Application
Dim PPpres As PowerPoint.Presentation
'Create a PP application and make it visible
Set PP = New PowerPoint.Application
PP.Visible = msoCTrue
'Open the presentation you wish to copy to
Set PPpres = PP.Presentations.Open("C:\work\test1.ppt")
'Set the shape you want to copy
Set Sh = Worksheets("Sheet1").Shapes(1)
'Copy the shape
Sh.CopyPicture Appearance:=xlScreen, Format:=xlPicture
'And paste it on the second slide
PPpres.Slides(2).Shapes.Paste
Set Sh = Nothing
Set PP = Nothing
Set PPpres = Nothing

End Sub

If you have an idea how to modify this, to be useful for what I need...
I know that my be simple but I'm new to VBA and don't know how to do it

Thanks in advance

**Andy Pope** · 08-18-2009, 04:25 AM

Please add code tags to your post.

You need to modify two parts of the existing code to use specific chartobject and slide numbers.
Then place a loop around the copy+paste code.

Please Login or Register  to view this content.

**hvsuhas** · 01-03-2013, 09:27 AM

You need to modify 3 parts of the existing code to use specific chartobject and slide numbers and the path where the excel is saved.

I have mentioned in the code as well to make it clear while editing it.

Below is the code:

Enjoy

Please Login or Register  to view this content.

**arlu1201** · 01-03-2013, 09:43 AM

hvsuhas,

Welcome to the forum and thank you for your solution.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.

**OllieB** · 01-03-2013, 09:53 AM

Coolin,

I once developed a solution where I created a template Powerpoint presentation. On various slides I placed shapes (of the desired dimensions etc), and in the alternative text of the shape I would enter a parameter that indicated where the contents for that shape where to be retrieved from the Excel Workbook from where I ran the process.

Once the information was retrieved from the workbook, the original shape on the Powerpoint slide was deleted and the desired object was created.

The fun part of the solution is that it allows for changes to the Excel Workbook and Powerpoint presentation contents without any need to change the underlying code. Depending on the stability of your requirements you way want to consider something dynamic like this.

**OllieB** · 01-03-2013, 10:30 AM

Hi Coolin,

Because I was bored, I have written something you might be able to use.

Please Login or Register  to view this content.

**sidfogla** · 08-06-2015, 09:01 AM

I am using the following code but the problem that I am having is that it is copying it as a picture. I want to retain the same formatting as it is in Excel and not copy it as a picture but an object. Could you please help?

Thanks in Advance.

Sub test_paste2()
Dim Sh
Dim PP As PowerPoint.Application
Dim PPpres As PowerPoint.Presentation
Dim lngSlideNumbers(3) As Long
Dim lngChartNumbers(3) As Long
Dim lngIndex As Long

lngSlideNumbers(1) = 6
lngSlideNumbers(2) = 10
lngSlideNumbers(3) = 12

lngChartNumbers(1) = 3
lngChartNumbers(2) = 5
lngChartNumbers(3) = 20

'Create a PP application and make it visible
Set PP = New PowerPoint.Application
PP.Visible = msoCTrue
'Open the presentation you wish to copy to
Set PPpres = PP.Presentations.Open("C:\Users\Desktop\Practice.pptx")

'For lngIndex = 1 To 3

'Set the shape you want to copy
Set Sh = Application.Worksheets("Graph").ChartObjects("Chart 3")
'Copy the shape
Sh.CopyPicture Appearance:=xlScreen, Format:=xlPicture

'And paste it on the sixth slide
PPpres.Slides(lngSlideNumbers(1)).Shapes.Paste

'Next

Set Sh = Nothing
Set PP = Nothing
Set PPpres = Nothing

End Sub

**ksujithkarun** · 08-07-2015, 07:57 AM

This is exactly what I was searching for, for a very long time. Cannot thank you enough for sharing this

**Prince Sethi** · 08-21-2015, 08:23 AM

Sub ChartToPresentation()
Dim oPPTApp As PowerPoint.Application
Dim oPPTFile As PowerPoint.Presentation
Dim oPPTShape As PowerPoint.Shape
Dim oPPTSlide As PowerPoint.Slide
Dim SlideNum As Integer
Set oPPTApp = New PowerPoint.Application
oPPTApp.Visible = True
oPPTApp.Presentations.Open Filename:="C:\Users\sethipri1\Documents\blank.ppt"
' specify the chart to copy and copy it
ThisWorkbook.Worksheets("formulation").ChartObjects(1).Select
If ActiveChart Is Nothing Then
MsgBox "Please select a chart and try again.", vbExclamation, _
"No Chart Selected"
Else
' Reference existing instance of PowerPoint

' Reference active presentation
Set PPPres = oPPTApp.ActivePresentation

oPPTApp.ActiveWindow.ViewType = ppViewSlide
' Reference active slide
Set oPPTSlide = PPPres.Slides _
(oPPTApp.ActiveWindow.Selection.SlideRange.SlideIndex)

' Copy chart as a picture

For i = 0 To 3
On Error Resume Next
ThisWorkbook.Worksheets("formulation").ChartObjects(i).Select
oPPTApp.ActiveWindow.ViewType = ppViewSlide
ActiveChart.CopyPicture Appearance:=xlScree, Size:=xlScreen, _
Format:=xlPicture

' Paste chart
oPPTSlide.Shapes.Paste.Select

Dim left1(3)
Dim top1(3)
Dim height1(3)
Dim width1(3)

left1(0) = 20: top1(0) = 70: width1(0) = 150: height1(0) = 120
left1(1) = 350: top1(1) = 70: width1(1) = 150: height1(1) = 120
left1(2) = 20: top1(2) = 300: width1(2) = 150: height1(2) = 120
left1(3) = 350: top1(3) = 300: width1(3) = 150: height1(3) = 120

' Align pasted chart

oPPTApp.ActiveWindow.Selection.ShapeRange.Left = left1(i)
oPPTApp.ActiveWindow.Selection.ShapeRange.Top = top1(i)
oPPTApp.ActiveWindow.Selection.ShapeRange.Width = height1(i)
oPPTApp.ActiveWindow.Selection.ShapeRange.Height = width1(i)
Next 'oPPTApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
'oPPTApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True

' Clean up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing
End If

End Sub

**Sartaxi** · 06-26-2019, 08:23 PM

Originally Posted by OllieB

Hi Coolin,

Because I was bored, I have written something you might be able to use.

OllieB (Bommel?), I know this post is ages old, but I still wanted to reach out to you, first of all to acknowledge how great it is you were willing to share this, but also because I am running into some issues.

I was trying to cobble up myself, but as I'm not really good at VBA (I get a long way slowly by googling and combining existing codes), this was an amazing step forward.
My colleague and I make a monthly chartpack with 50 slides, with between 1-3 charts on each slide. You can imagine this code would save us loads of time!

However, I'm running into some problems:
1) I notice that for slides with 2 or more charts, it consistently skips copying the second chart on the page (right one if page contains 2 charts, middle if 3 etc). The resulting pack still has the placeholder shapes with the (at)REPLACE|xxxxx|xxxxx code. I couldn't find anything in the code that would hint at why it would be doing this, so not sure what this could be.
2) A few times I got an error "Run-time error '-2147024809 (80070057)': The item with the specified name wasn't found." - I think the intent of your code was to give the shape and worksheet in a MsgBox, but this doesn't seem to work, any idea how I can fix that? (by elimination I found the charts that were the problem, but the error msg doesn't seem to work as intended)

Given it's been a while since you wrote the code, I really hope you (or someone else) would still have time/energy to help me (esp with problem 1).

thanks!

Sartaxi

**6StringJazzer** · 06-26-2019, 08:52 PM

Sartaxi, welcome to the forum.

First, to find your Office version open Excel, go to File, Account. You will see your version, unless you have a very old version, then the sequence is something else that I don't even remember.

Second: Administrative Note:

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original. Please start a new thread - See Forum rule #4

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Attach your Excel file. The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

Because this thread is so old, and answered over 6 years ago, I am going to close it. PM me if you need further assistance.