Im trying to create a macro to export Multiple Charts from a Single pivot Table.
My Table looks something like this (This is just an example, the number of categories like 40-50+ and items keep changing every month)
Basically i need to create a stack 100% bar chart, with percentage labels, but i am unable to create a loop,
wondering if there is any way to do this?
I am assuming we may need to split the data into multiple sheets first? i found this Split Data into Multiple Worksheets Based on Column - Free Excel Tutorial (excelhow.net)
i got this macro to export each chart to the ppt :
Code
Sub SelectedSheetsPowerPoint()
Dim wsItem As Worksheet
Dim chtObj As ChartObject
For Each wsItem In ThisWorkbook.Worksheets
For Each chtObj In wsItem.ChartObjects
wsItem.Activate
'~~> Code here to copy it to the powerpoint
'~~> Same for deleting it
'First we declare the variables we will be using
Dim newPowerPoint As PowerPoint.Application
Dim activeSlide As PowerPoint.Slide
Dim cht As Excel.ChartObject
'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
cht.Select
ActiveChart.ChartArea.Copy
activeSlide.Shapes.PasteSpecial(DataType:=ppPasteDefault, Link:=False).Select
'Set the title of the slide the same as the title of the chart
activeSlide.Shapes(1).TextFrame.TextRange.Text = cht.Chart.ChartTitle.Text
'Adjust the positioning of the Chart on Powerpoint Slide
newPowerPoint.ActiveWindow.Selection.ShapeRange.Left = 75
newPowerPoint.ActiveWindow.Selection.ShapeRange.Top = 120
activeSlide.Shapes(2).Width = 200
activeSlide.Shapes(2).Left = 505
'loop through each chart in !!activesheet!! and move each into a new slide!
Next
'start pp, can add preset headings for power point here
'AppActivate ("Microsoft PowerPoint")
Set activeSlide = Nothing
Set newPowerPoint = Nothing
DoEvents
Next
Next
End SubCould someone help me on how do i go about this and how do i loop thru? i am struggling to get the create chart part correct