Need to assign specific charts to variables

[GeeWhiz7]

Hi Folks,

I am trying to figure out the syntax to assign individual charts to individual variables in my macro rather than loop through each chart to send them to PowerPoint.

Using the syntax below throws a run-time error 91, Object variable or With block variable not set even though I have seen this syntax in other uses.

I have a lot of charts in a worksheet so would like to select (not manually) two of them and assign to variables to copy and paste elsewhere.

Any ideas?

Dim chtA As Chart 'Excel.ChartObject
Dim chtB As Chart ' Excel.ChartObject
     
chtA = Sheets("Sheet1").ChartObjects("Chart 33").Chart
chtB = Sheets("Sheet1").ChartObjects("Chart 35").Chart

The error point

 

[Domenic]

Since you're assigning an object to your variable, you need to use the Set keyword . . .

set chtA = Sheets("Sheet1").ChartObjects("Chart 33").Chart
set chtB = Sheets("Sheet1").ChartObjects("Chart 35").Chart

Hope this helps!

 

[GeeWhiz7]

Since you're assigning an object to your variable, you need to use the Set keyword . . .

set chtA = Sheets("Sheet1").ChartObjects("Chart 33").Chart
set chtB = Sheets("Sheet1").ChartObjects("Chart 35").Chart

Hope this helps!

Hi Domenic, thanks for the fast reply. I think using Set is what threw me off in the first place. I'm still learning vba so there is that, but when I used set and then later try to select the chart variable, I get a '1004' error / Method 'Select' of Object _Chart failed....

The full code that I wanted to modify and not originally mine, but from Chandoo.org works great looping through all charts, but I only want two (33 and 35) and then I think I can figure out how to manipulate them.

Sub CreatePowerPoint()

 'Add a reference to the Microsoft PowerPoint Library by:
    '1. Go to Tools in the VBA menu
    '2. Click on Reference
    '3. Scroll down to Microsoft PowerPoint X.0 Object Library, check the box, and press Okay
 
    'First we declare the variables we will be using
        Dim newPowerPoint As PowerPoint.Application
        Dim activeSlide As PowerPoint.Slide
        Dim chtA As Chart 'Excel.ChartObject
        Dim chtB As Chart ' Excel.ChartObject
     
        Set chtA = Sheets("Sheet1").ChartObjects("Chart 33").Chart
        Set chtB = Sheets("Sheet1").ChartObjects("Chart 35").Chart
     
     'Look for existing instance
        On Error Resume Next
        Set newPowerPoint = GetObject(, "PowerPoint.Application")
        On Error GoTo 0
     
    'Let's create a new PowerPoint
        If newPowerPoint Is Nothing Then
            Set newPowerPoint = New PowerPoint.Application
        End If
    'Make a presentation in PowerPoint
        If newPowerPoint.Presentations.Count = 0 Then
            newPowerPoint.Presentations.Add
        End If
     
    'Show the PowerPoint
        newPowerPoint.Visible = True
    
    'Loop through each chart in the Excel worksheet and paste them into the PowerPoint
'        For Each cht In ActiveSheet.ChartObjects

         
        'Add a new slide where we will paste the chart
            newPowerPoint.ActivePresentation.Slides.Add newPowerPoint.ActivePresentation.Slides.Count + 1, ppLayoutText
            newPowerPoint.ActiveWindow.View.GotoSlide newPowerPoint.ActivePresentation.Slides.Count
            Set activeSlide = newPowerPoint.ActivePresentation.Slides(newPowerPoint.ActivePresentation.Slides.Count)
                
        'Copy the chart and paste it into the PowerPoint as a Metafile Picture
            chtA.Select  'I get the error here'
            ActiveChart.ChartArea.Copy
            activeSlide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select
            newPowerPoint.ActiveWindow.Selection.ShapeRange.Left = 15
            newPowerPoint.ActiveWindow.Selection.ShapeRange.Top = 125
        
            activeSlide.Shapes(2).Width = 200
            activeSlide.Shapes(2).Left = 505
            
'        Next
     
'    AppActivate ("Microsoft PowerPoint")
    Set activeSlide = Nothing
    Set newPowerPoint = Nothing
     
End Sub

 

[RoryA]

You don't need to select it - just use the chart variable instead of Activechart.

 

[GeeWhiz7]

You don't need to select it - just use the chart variable instead of Activechart.

Well that works and now that you point it out it seems obvious . Thank you RoryA and Domenic for your help.