Method 'ChartObjects' of object'_Worksheet' failed

T

Todd77

New Member
Joined
Aug 20, 2019
Messages
12
Hi all,

I'm trying to pull charts from excel and paste them into Powerpoint. I have a total of four slides and everything works as it should for three of them. However, when I get to the final chart, something breaks down and I get the following error message: Method 'ChartObjects' of object'_Worksheet' failed.

Adding to the confusion is the fact that there was no issue three weeks ago. I haven't made any alterations to the code since but am currently having problems.

The code is below. I have coloured in bold red the part where the error pops up.

Sub CreateNewPresentation_EU_Total()


Dim ppApp As PowerPoint.Application
Dim ppPres As PowerPoint.Presentation
Dim ppSlide As PowerPoint.Slide
Dim EUChart1 As Excel.ChartObject
Dim EUChart2 As Excel.ChartObject
Dim EUChart3 As Excel.ChartObject
Dim EUChart4 As Excel.ChartObject
Dim EUChart5 As Excel.ChartObject
Dim EUChart6 As Excel.ChartObject
Dim EUChart7 As Excel.ChartObject
Dim EUChart8 As Excel.ChartObject
Dim EUChart9 As Excel.ChartObject
Dim EUChart10 As Excel.ChartObject
Dim EUSheet1 As Excel.Worksheet
Dim EUData As Excel.Range
Dim ppTextbox As PowerPoint.Shape

Set EUSheet1 = ActiveWorkbook.Sheets("Charts+Tables")
Set EUData = EUSheet1.Range("a44:i47")
Set ppApp = New PowerPoint.Application
Set EUChart1 = EUSheet1.ChartObjects(1)


ppApp.Visible = True
ppApp.Activate


Set ppPres = ppApp.Presentations.Add
Set ppSlide = ppPres.Slides.Add(1, ppLayoutTitle)


ppSlide.Shapes(1).TextFrame.TextRange = "EU Total"
ppSlide.Shapes(2).TextFrame.TextRange = "Presenter Name, Title"


Set ppSlide = ppPres.Slides.Add(2, ppLayoutBlank)
ppSlide.Select


'** slide 1 EU Total - Product Domain **

EUChart1.Copy
ppSlide.Shapes.Paste
ppSlide.Shapes(1).Left = 50
ppSlide.Shapes(1).Top = 60
ppSlide.Shapes(1).Height = 465
ppSlide.Shapes(1).Width = 350

Set EUData = EUSheet1.Range("a107:d110")
Set EUChart2 = EUSheet1.ChartObjects(2)

EUChart2.Copy
ppSlide.Shapes.Paste
ppSlide.Shapes(2).Left = 525
ppSlide.Shapes(2).Top = 60
ppSlide.Shapes(2).Height = 465
ppSlide.Shapes(2).Width = 350



Range("l3").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(3).Left = 385
ppSlide.Shapes(3).Top = 60
ppSlide.Shapes(3).Height = 65
ppSlide.Shapes(3).Width = 80

Range("l8").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(4).Left = 385
ppSlide.Shapes(4).Top = 110
ppSlide.Shapes(4).Height = 65
ppSlide.Shapes(4).Width = 80

Range("l13").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(5).Left = 385
ppSlide.Shapes(5).Top = 160
ppSlide.Shapes(5).Height = 65
ppSlide.Shapes(5).Width = 80

Range("l18").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(6).Left = 385
ppSlide.Shapes(6).Top = 210
ppSlide.Shapes(6).Height = 65
ppSlide.Shapes(6).Width = 80

Range("l23").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(7).Left = 385
ppSlide.Shapes(7).Top = 260
ppSlide.Shapes(7).Height = 65
ppSlide.Shapes(7).Width = 80

Range("l28").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(8).Left = 385
ppSlide.Shapes(8).Top = 310
ppSlide.Shapes(8).Height = 65
ppSlide.Shapes(8).Width = 80

Range("l33").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(9).Left = 385
ppSlide.Shapes(9).Top = 360
ppSlide.Shapes(9).Height = 65
ppSlide.Shapes(9).Width = 80

Range("l38").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(10).Left = 385
ppSlide.Shapes(10).Top = 410
ppSlide.Shapes(10).Height = 65
ppSlide.Shapes(10).Width = 80

Range("l43").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(11).Left = 385
ppSlide.Shapes(11).Top = 460
ppSlide.Shapes(11).Height = 65
ppSlide.Shapes(11).Width = 80

'** slide 1 EU Total - Revenue Type **

Range("l75").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(12).Left = 845
ppSlide.Shapes(12).Top = 150
ppSlide.Shapes(12).Height = 65
ppSlide.Shapes(12).Width = 80

Range("l80").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(13).Left = 845
ppSlide.Shapes(13).Top = 200
ppSlide.Shapes(13).Height = 65
ppSlide.Shapes(13).Width = 80

Range("l85").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(14).Left = 845
ppSlide.Shapes(14).Top = 250
ppSlide.Shapes(14).Height = 65
ppSlide.Shapes(14).Width = 80

Range("l90").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(15).Left = 845
ppSlide.Shapes(15).Top = 300
ppSlide.Shapes(15).Height = 65
ppSlide.Shapes(15).Width = 80


'** slide 2 EU Total - Sales Channel **

Set ppSlide = ppPres.Slides.Add(3, ppLayoutBlank)
ppSlide.Select


Set EUData = EUSheet1.Range("a188:c191")
Set EUChart3 = EUSheet1.ChartObjects(3)


EUChart3.Copy
ppSlide.Shapes.Paste
ppSlide.Shapes(1).Left = 50
ppSlide.Shapes(1).Top = 60
ppSlide.Shapes(1).Height = 465
ppSlide.Shapes(1).Width = 350

Set EUData = EUSheet1.Range("g260:i263")
Set EUChart4 = EUSheet1.ChartObjects(4)

EUChart4.Copy
ppSlide.Shapes.Paste
ppSlide.Shapes(2).Left = 525
ppSlide.Shapes(2).Top = 60
ppSlide.Shapes(2).Height = 230
ppSlide.Shapes(2).Width = 350

Set EUData = EUSheet1.Range("a260:c263")
Set EUChart5 = EUSheet1.ChartObjects(5)

EUChart5.Copy
ppSlide.Shapes.Paste
ppSlide.Shapes(3).Left = 525
ppSlide.Shapes(3).Top = 300
ppSlide.Shapes(3).Height = 230
ppSlide.Shapes(3).Width = 350

Range("b269").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(4).Left = 80
ppSlide.Shapes(4).Top = 350
ppSlide.Shapes(4).Height = 70
ppSlide.Shapes(4).Width = 90

Range("b271").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(5).Left = 190
ppSlide.Shapes(5).Top = 350
ppSlide.Shapes(5).Height = 70
ppSlide.Shapes(5).Width = 90

Range("b273").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(6).Left = 290
ppSlide.Shapes(6).Top = 350
ppSlide.Shapes(6).Height = 70
ppSlide.Shapes(6).Width = 90

Range("h277").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(7).Left = 565
ppSlide.Shapes(7).Top = 210
ppSlide.Shapes(7).Height = 70
ppSlide.Shapes(7).Width = 90

Range("h279").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(8).Left = 665
ppSlide.Shapes(8).Top = 210
ppSlide.Shapes(8).Height = 70
ppSlide.Shapes(8).Width = 90

Range("h281").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(9).Left = 765
ppSlide.Shapes(9).Top = 210
ppSlide.Shapes(9).Height = 70
ppSlide.Shapes(9).Width = 90

Range("h269").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(10).Left = 565
ppSlide.Shapes(10).Top = 400
ppSlide.Shapes(10).Height = 70
ppSlide.Shapes(10).Width = 90

Range("h271").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(11).Left = 665
ppSlide.Shapes(11).Top = 400
ppSlide.Shapes(11).Height = 70
ppSlide.Shapes(11).Width = 90

Range("h273").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(12).Left = 765
ppSlide.Shapes(12).Top = 400
ppSlide.Shapes(12).Height = 70
ppSlide.Shapes(12).Width = 90

Range("l141").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(13).Left = 385
ppSlide.Shapes(13).Top = 225
ppSlide.Shapes(13).Height = 65
ppSlide.Shapes(13).Width = 80

Range("l146").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(14).Left = 385
ppSlide.Shapes(14).Top = 275
ppSlide.Shapes(14).Height = 65
ppSlide.Shapes(14).Width = 80

Range("l151").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(15).Left = 385
ppSlide.Shapes(15).Top = 325
ppSlide.Shapes(15).Height = 65
ppSlide.Shapes(15).Width = 80


Range("l182").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(16).Left = 845
ppSlide.Shapes(16).Top = 125
ppSlide.Shapes(16).Height = 65
ppSlide.Shapes(16).Width = 80

Range("l187").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(17).Left = 845
ppSlide.Shapes(17).Top = 175
ppSlide.Shapes(17).Height = 65
ppSlide.Shapes(17).Width = 80

Range("l192").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(18).Left = 845
ppSlide.Shapes(18).Top = 225
ppSlide.Shapes(18).Height = 65
ppSlide.Shapes(18).Width = 80

Range("l197").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(19).Left = 845
ppSlide.Shapes(19).Top = 325
ppSlide.Shapes(19).Height = 65
ppSlide.Shapes(19).Width = 80

Range("l202").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(20).Left = 845
ppSlide.Shapes(20).Top = 375
ppSlide.Shapes(20).Height = 65
ppSlide.Shapes(20).Width = 80

Range("l207").CurrentRegion.CopyPicture

ppSlide.Shapes.Paste
ppSlide.Shapes(21).Left = 845
ppSlide.Shapes(21).Top = 425
ppSlide.Shapes(21).Height = 65
ppSlide.Shapes(21).Width = 80

Set ppSlide = ppPres.Slides.Add(4, ppLayoutBlank)
ppSlide.Select

Set EUData = EUSheet1.Range("a338:h341")
Set EUChart8 = EUSheet1.ChartObjects(8)


EUChart8.Copy
ppSlide.Shapes.Paste
ppSlide.Shapes(1).Left = 50
ppSlide.Shapes(1).Top = 60
ppSlide.Shapes(1).Height = 465
ppSlide.Shapes(1).Width = 350
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
Hi there. My initial guess is that there is no ChartObjects(8). Are you sure there are 8 (or more) objects on that sheet?
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Excel to PowerPoint - Was working, not generating error....
Replies
1
Views
688
BuJay
B
K
  • Question
VBA: PowerPoint - Copy slides from one presentation to another
Replies
1
Views
2K
krehkop
K
A
  • Solved
Excel to PowerPoint error ( Object variable or with block not set )
Replies
2
Views
1K
Akbarov
A
N
  • Question
changing the color pallet of a chart with VBA
Replies
1
Views
103
rlv01
R
F
  • Question
How to get optionbutton values from two different groups
Replies
2
Views
242
NoSparks
N

Forum statistics

Threads
1,214,887
Messages
6,122,095
Members
449,064
Latest member
Danger_SF

Share this page

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