Excel VBA to loop through slicer items and run code to create powerpoint

Question

So, I have an KPI dashboard where is use a VBA to export each country, region & site to powerpoint. since there is a lot of them and i do the filtering manually from slicers i´d like to make this more efficient.

There is 3 slicers, one contains regions, one contains country's and the last one contains sites.

The VBA triggered from an command button is the following, I would like to add another button that allows me to loop the VBA for the first slicer each region item with data, then slicer 2 for all countrys with data and last slicer for each site with data. he reason to keep the first button is that I would still need some times to do only one site, or one country.

I´m no expert to VBA and the code below probably could be better, but it works. I'm just thinking of how to start with the loop.

Dim papp As PowerPoint.Application
Dim pppt As PowerPoint.Presentation
Dim psld As PowerPoint.Slide
Dim filnam As String
Dim filnam2 As String

Set papp = New PowerPoint.Application
papp.Visible = True

Set pppt = papp.Presentations.Add

Set psld = pppt.Slides.Add(1, ppLayoutBlank)
psld.BackgroundStyle = msoBackgroundStylePreset2

papp.Windows(1).Activate

psld.Shapes.AddShape(msoShapeRectangle, 0, 0, 10, 10).Name = "Title_background"
psld.Shapes("title_background").Fill.ForeColor.RGB = RGB(255, 255, 255)
psld.Shapes("title_background").Line.Visible = msoFalse
psld.Shapes("title_background").Width = 960.09448819
psld.Shapes("title_background").Height = 59.811023622
psld.Shapes("title_background").Shadow.Type = msoShadow22


psld.Shapes.AddTextbox(msoTextOrientationHorizontal, 162.42519685, 5.1023622047, 20, 20).Name = "Title"
psld.Shapes("Title").TextFrame.TextRange.Text = Range("y9").Value
psld.Shapes("Title").Height = 49.606299213
psld.Shapes("Title").Width = 226.77165354
psld.Shapes("Title").TextFrame.TextRange.Font.Size = 24
psld.Shapes("Title").TextFrame.TextRange.Font.Name = "Abadi"
psld.Shapes("Title").TextFrame.TextRange.Font.Color.RGB = RGB(42, 41, 92)
psld.Shapes("Title").TextFrame2.TextRange.Font.Spacing = 3
psld.Shapes("Title").TextFrame2.TextRange.Font.Bold = msoTrue

psld.Shapes.AddTextbox(msoTextOrientationHorizontal, 161.85826772, 32.031496063, 20, 20).Name = "subTitle"
psld.Shapes("subTitle").TextFrame.TextRange.Text = Range("y10").Value
psld.Shapes("subTitle").Height = 49.606299213
psld.Shapes("subTitle").Width = 226.77165354
psld.Shapes("subTitle").TextFrame.TextRange.Font.Size = 11
psld.Shapes("subTitle").TextFrame.TextRange.Font.Name = "Abadi"
psld.Shapes("subTitle").TextFrame.TextRange.Font.Color.RGB = RGB(42, 41, 92)
psld.Shapes("subTitle").TextFrame2.TextRange.Font.Spacing = 2

Worksheets("Dashboard").Shapes("sdxlogo").Copy
papp.Windows(1).Activate
psld.Shapes.PasteSpecial
psld.Shapes("sdxlogo").Top = 0
psld.Shapes("sdxlogo").Left = 0


Worksheets("Dashboard").Shapes("tplogo").Copy
papp.Windows(1).Activate
psld.Shapes.PasteSpecial
psld.Shapes("tplogo").Top = 7.3700787402
psld.Shapes("tplogo").Left = 908.78740157

psld.Shapes.AddShape(msoShapeRectangle, 0, 0, 10, 10).Name = "shpbg1"
psld.Shapes("shpbg1").Fill.ForeColor.RGB = RGB(255, 255, 255)
psld.Shapes("shpbg1").Line.Visible = msoFalse
psld.Shapes("shpbg1").Width = 226.77165354
psld.Shapes("shpbg1").Height = 141.73228346
psld.Shapes("shpbg1").Top = 72
psld.Shapes("shpbg1").Left = 19.559055118

psld.Shapes.AddShape(msoShapeRectangle, 0, 0, 10, 10).Name = "shpbg2"
psld.Shapes("shpbg2").Fill.ForeColor.RGB = RGB(255, 255, 255)
psld.Shapes("shpbg2").Line.Visible = msoFalse
psld.Shapes("shpbg2").Width = 226.77165354
psld.Shapes("shpbg2").Height = 141.73228346
psld.Shapes("shpbg2").Top = 72
psld.Shapes("shpbg2").Left = 252

psld.Shapes.AddShape(msoShapeRectangle, 0, 0, 10, 10).Name = "shpbg3"
psld.Shapes("shpbg3").Fill.ForeColor.RGB = RGB(255, 255, 255)
psld.Shapes("shpbg3").Line.Visible = msoFalse
psld.Shapes("shpbg3").Width = 226.77165354
psld.Shapes("shpbg3").Height = 141.73228346
psld.Shapes("shpbg3").Top = 72
psld.Shapes("shpbg3").Left = 484.44094488

psld.Shapes.AddShape(msoShapeRectangle, 0, 0, 10, 10).Name = "shpbg4"
psld.Shapes("shpbg4").Fill.ForeColor.RGB = RGB(255, 255, 255)
psld.Shapes("shpbg4").Line.Visible = msoFalse
psld.Shapes("shpbg4").Width = 226.77165354
psld.Shapes("shpbg4").Height = 141.73228346
psld.Shapes("shpbg4").Top = 72
psld.Shapes("shpbg4").Left = 716.88188976

psld.Shapes.AddShape(msoShapeRectangle, 0, 0, 10, 10).Name = "shpbg5"
psld.Shapes("shpbg5").Fill.ForeColor.RGB = RGB(255, 255, 255)
psld.Shapes("shpbg5").Line.Visible = msoFalse
psld.Shapes("shpbg5").Width = 226.77165354
psld.Shapes("shpbg5").Height = 141.73228346
psld.Shapes("shpbg5").Top = 224.22047244
psld.Shapes("shpbg5").Left = 19.559055118

psld.Shapes.AddShape(msoShapeRectangle, 0, 0, 10, 10).Name = "shpbg6"
psld.Shapes("shpbg6").Fill.ForeColor.RGB = RGB(255, 255, 255)
psld.Shapes("shpbg6").Line.Visible = msoFalse
psld.Shapes("shpbg6").Width = 226.77165354
psld.Shapes("shpbg6").Height = 141.73228346
psld.Shapes("shpbg6").Top = 224.22047244
psld.Shapes("shpbg6").Left = 252

psld.Shapes.AddShape(msoShapeRectangle, 0, 0, 10, 10).Name = "shpbg7"
psld.Shapes("shpbg7").Fill.ForeColor.RGB = RGB(255, 255, 255)
psld.Shapes("shpbg7").Line.Visible = msoFalse
psld.Shapes("shpbg7").Width = 226.77165354
psld.Shapes("shpbg7").Height = 141.73228346
psld.Shapes("shpbg7").Top = 224.22047244
psld.Shapes("shpbg7").Left = 484.44094488

psld.Shapes.AddShape(msoShapeRectangle, 0, 0, 10, 10).Name = "shpbg8"
psld.Shapes("shpbg8").Fill.ForeColor.RGB = RGB(255, 255, 255)
psld.Shapes("shpbg8").Line.Visible = msoFalse
psld.Shapes("shpbg8").Width = 226.77165354
psld.Shapes("shpbg8").Height = 141.73228346
psld.Shapes("shpbg8").Top = 224.22047244
psld.Shapes("shpbg8").Left = 716.88188976

Worksheets("Dashboard").Shapes("rm_responded").Copy
papp.Windows(1).Activate
psld.Shapes.PasteSpecial ppPasteEnhancedMetafile, link = False
psld.Shapes("Picture 14").Top = 109.41732283
psld.Shapes("Picture 14").Left = 456.37795276

Worksheets("Dashboard").Shapes("rm_completed").Copy
papp.Windows(1).Activate
psld.Shapes.PasteSpecial ppPasteEnhancedMetafile, link = False
psld.Shapes("Picture 15").Top = 109.41732283
psld.Shapes("Picture 15").Left = 687.68503937

Worksheets("Dashboard").Shapes("pmmand").Copy
papp.Windows(1).Activate
psld.Shapes.PasteSpecial ppPasteEnhancedMetafile, link = False
psld.Shapes("Picture 16").Top = 109.41732283
psld.Shapes("Picture 16").Left = 223.37007874

Worksheets("Dashboard").Shapes("pmstat").Copy
papp.Windows(1).Activate
psld.Shapes.PasteSpecial ppPasteEnhancedMetafile, link = False
psld.Shapes("Picture 17").Top = 109.41732283
psld.Shapes("Picture 17").Left = -9.3543307087

Worksheets("Dashboard").Range("AE4").Copy
papp.Windows(1).Activate
psld.Shapes.PasteSpecial ppPasteEnhancedMetafile, link = False
psld.Shapes("Picture 18").Top = 170.64566929
psld.Shapes("Picture 18").Left = 83.622047244
psld.Shapes("Picture 18").Width = 78.803149606
psld.Shapes("Picture 18").Height = 23.811023622

Worksheets("Dashboard").Range("AE5").Copy
papp.Windows(1).Activate
psld.Shapes.PasteSpecial ppPasteEnhancedMetafile, link = False
psld.Shapes("Picture 19").Top = 170.64566929
psld.Shapes("Picture 19").Left = 316.91338583
psld.Shapes("Picture 19").Width = 78.803149606
psld.Shapes("Picture 19").Height = 23.811023622

Worksheets("Dashboard").Range("AE6").Copy
papp.Windows(1).Activate
psld.Shapes.PasteSpecial ppPasteEnhancedMetafile, link = False
psld.Shapes("Picture 20").Top = 170.64566929
psld.Shapes("Picture 20").Left = 548.22047244
psld.Shapes("Picture 20").Width = 78.803149606
psld.Shapes("Picture 20").Height = 23.811023622

Worksheets("Dashboard").Range("AE7").Copy
papp.Windows(1).Activate
psld.Shapes.PasteSpecial ppPasteEnhancedMetafile, link = False
psld.Shapes("Picture 21").Top = 170.64566929
psld.Shapes("Picture 21").Left = 780.94488189
psld.Shapes("Picture 21").Width = 78.803149606
psld.Shapes("Picture 21").Height = 23.811023622

Worksheets("Dashboard").Shapes("procrt").Copy
papp.Windows(1).Activate
psld.Shapes.PasteSpecial ppPasteEnhancedMetafile, link = False
psld.Shapes("Picture 22").Top = 253.98425197
psld.Shapes("Picture 22").Left = 43.653543307

Worksheets("Dashboard").Shapes("sfcrt").Copy
papp.Windows(1).Activate
psld.Shapes.PasteSpecial ppPasteEnhancedMetafile, link = False
psld.Shapes("Picture 23").Top = 253.98425197
psld.Shapes("Picture 23").Left = 276.09448819

Worksheets("Dashboard").Shapes("saf_completed").Copy
papp.Windows(1).Activate
psld.Shapes.PasteSpecial ppPasteEnhancedMetafile, link = False
psld.Shapes("Picture 24").Top = 260.22047244
psld.Shapes("Picture 24").Left = 456.37795276

Worksheets("Dashboard").Range("AE10").Copy
papp.Windows(1).Activate
psld.Shapes.PasteSpecial ppPasteEnhancedMetafile, link = False
psld.Shapes("Picture 25").Top = 321.4488189
psld.Shapes("Picture 25").Left = 548.22047244
psld.Shapes("Picture 25").Width = 78.803149606
psld.Shapes("Picture 25").Height = 23.811023622

psld.Shapes.AddTextbox(msoTextOrientationHorizontal, 16.440944882, 69.165354331, 20, 20).Name = "pmsta"
psld.Shapes("pmsta").TextFrame.TextRange.Text = ("Preventive Maintenance - Statutory")
psld.Shapes("pmsta").Height = 49.606299213
psld.Shapes("pmsta").Width = 226.77165354
psld.Shapes("pmsta").TextFrame.TextRange.Font.Size = 9
psld.Shapes("pmsta").TextFrame.TextRange.Font.Name = "Abadi"
psld.Shapes("pmsta").TextFrame.TextRange.Font.Color.RGB = RGB(42, 41, 92)

psld.Shapes.AddTextbox(msoTextOrientationHorizontal, 249.4488189, 69.165354331, 20, 20).Name = "pmmand"
psld.Shapes("pmmand").TextFrame.TextRange.Text = ("Preventive Maintenance – Critical Assets")
psld.Shapes("pmmand").Height = 49.606299213
psld.Shapes("pmmand").Width = 226.77165354
psld.Shapes("pmmand").TextFrame.TextRange.Font.Size = 9
psld.Shapes("pmmand").TextFrame.TextRange.Font.Name = "Abadi"
psld.Shapes("pmmand").TextFrame.TextRange.Font.Color.RGB = RGB(42, 41, 92)

psld.Shapes.AddTextbox(msoTextOrientationHorizontal, 479.90551181, 69.165354331, 20, 20).Name = "rmres"
psld.Shapes("rmres").TextFrame.TextRange.Text = ("Reactive Maintenance - Response time")
psld.Shapes("rmres").Height = 49.606299213
psld.Shapes("rmres").Width = 226.77165354
psld.Shapes("rmres").TextFrame.TextRange.Font.Size = 9
psld.Shapes("rmres").TextFrame.TextRange.Font.Name = "Abadi"
psld.Shapes("rmres").TextFrame.TextRange.Font.Color.RGB = RGB(42, 41, 92)

psld.Shapes.AddTextbox(msoTextOrientationHorizontal, 712.91338583, 69.165354331, 20, 20).Name = "rmcom"
psld.Shapes("rmcom").TextFrame.TextRange.Text = ("Reactive Maintenance - Completion time")
psld.Shapes("rmcom").Height = 49.606299213
psld.Shapes("rmcom").Width = 226.77165354
psld.Shapes("rmcom").TextFrame.TextRange.Font.Size = 9
psld.Shapes("rmcom").TextFrame.TextRange.Font.Name = "Abadi"
psld.Shapes("rmcom").TextFrame.TextRange.Font.Color.RGB = RGB(42, 41, 92)

psld.Shapes.AddTextbox(msoTextOrientationHorizontal, 16.440944882, 220.81889764, 20, 20).Name = "pro"
psld.Shapes("pro").TextFrame.TextRange.Text = ("Proactive Work orders")
psld.Shapes("pro").Height = 49.606299213
psld.Shapes("pro").Width = 226.77165354
psld.Shapes("pro").TextFrame.TextRange.Font.Size = 9
psld.Shapes("pro").TextFrame.TextRange.Font.Name = "Abadi"
psld.Shapes("pro").TextFrame.TextRange.Font.Color.RGB = RGB(42, 41, 92)

psld.Shapes.AddTextbox(msoTextOrientationHorizontal, 249.4488189, 220.81889764, 20, 20).Name = "saf"
psld.Shapes("saf").TextFrame.TextRange.Text = ("Safety walks")
psld.Shapes("saf").Height = 49.606299213
psld.Shapes("saf").Width = 226.77165354
psld.Shapes("saf").TextFrame.TextRange.Font.Size = 9
psld.Shapes("saf").TextFrame.TextRange.Font.Name = "Abadi"
psld.Shapes("saf").TextFrame.TextRange.Font.Color.RGB = RGB(42, 41, 92)

psld.Shapes.AddTextbox(msoTextOrientationHorizontal, 479.90551181, 220.81889764, 20, 20).Name = "can"
psld.Shapes("can").TextFrame.TextRange.Text = ("Corrective and preventative actions")
psld.Shapes("can").Height = 49.606299213
psld.Shapes("can").Width = 226.77165354
psld.Shapes("can").TextFrame.TextRange.Font.Size = 9
psld.Shapes("can").TextFrame.TextRange.Font.Name = "Abadi"
psld.Shapes("can").TextFrame.TextRange.Font.Color.RGB = RGB(42, 41, 92)

psld.Shapes.AddTextbox(msoTextOrientationHorizontal, 712.91338583, 220.81889764, 20, 20).Name = "haz"
psld.Shapes("haz").TextFrame.TextRange.Text = ("Hazard reporting")
psld.Shapes("haz").Height = 49.606299213
psld.Shapes("haz").Width = 226.77165354
psld.Shapes("haz").TextFrame.TextRange.Font.Size = 9
psld.Shapes("haz").TextFrame.TextRange.Font.Name = "Abadi"
psld.Shapes("haz").TextFrame.TextRange.Font.Color.RGB = RGB(42, 41, 92)

psld.Shapes.AddTextbox(msoTextOrientationHorizontal, 202.11023622, 183.96850394, 20, 20).Name = "pmstatrg"
psld.Shapes("pmstatrg").TextFrame.TextRange.Text = ("100%")
psld.Shapes("pmstatrg").Height = 49.606299213
psld.Shapes("pmstatrg").Width = 100.77165354
psld.Shapes("pmstatrg").TextFrame.TextRange.Font.Size = 7
psld.Shapes("pmstatrg").TextFrame.TextRange.Font.Name = "Calibri"
psld.Shapes("pmstatrg").TextFrame.TextRange.Font.Color.RGB = RGB(64, 64, 64)

psld.Shapes.AddTextbox(msoTextOrientationHorizontal, 435.11811024, 183.96850394, 20, 20).Name = "pmmantrg"
psld.Shapes("pmmantrg").TextFrame.TextRange.Text = ("100%")
psld.Shapes("pmmantrg").Height = 49.606299213
psld.Shapes("pmmantrg").Width = 100.77165354
psld.Shapes("pmmantrg").TextFrame.TextRange.Font.Size = 7
psld.Shapes("pmmantrg").TextFrame.TextRange.Font.Name = "Calibri"
psld.Shapes("pmmantrg").TextFrame.TextRange.Font.Color.RGB = RGB(64, 64, 64)

psld.Shapes.AddTextbox(msoTextOrientationHorizontal, 666.42519685, 171.21259843, 20, 20).Name = "rmrestrg"
psld.Shapes("rmrestrg").TextFrame.TextRange.Text = ("95%")
psld.Shapes("rmrestrg").Height = 49.606299213
psld.Shapes("rmrestrg").Width = 100.77165354
psld.Shapes("rmrestrg").TextFrame.TextRange.Font.Size = 7
psld.Shapes("rmrestrg").TextFrame.TextRange.Font.Name = "Calibri"
psld.Shapes("rmrestrg").TextFrame.TextRange.Font.Color.RGB = RGB(64, 64, 64)

psld.Shapes.AddTextbox(msoTextOrientationHorizontal, 890.07874016, 149.95275591, 20, 20).Name = "rmcomptrg"
psld.Shapes("rmcomptrg").TextFrame.TextRange.Text = ("85%")
psld.Shapes("rmcomptrg").Height = 49.606299213
psld.Shapes("rmcomptrg").Width = 100.77165354
psld.Shapes("rmcomptrg").TextFrame.TextRange.Font.Size = 7
psld.Shapes("rmcomptrg").TextFrame.TextRange.Font.Name = "Calibri"
psld.Shapes("rmcomptrg").TextFrame.TextRange.Font.Color.RGB = RGB(64, 64, 64)

psld.Shapes.AddShape(msoShapeRectangle, 0, 0, 10, 10).Name = "shpbg9"
psld.Shapes("shpbg9").Fill.ForeColor.RGB = RGB(255, 255, 255)
psld.Shapes("shpbg9").Line.Visible = msoFalse
psld.Shapes("shpbg9").Width = 226.77165354
psld.Shapes("shpbg9").Height = 141.73228346
psld.Shapes("shpbg9").Top = 224.22047244
psld.Shapes("shpbg9").Left = 716.88188976

filnam = Range("y09")
filnam2 = Range("y10")

pppt.SaveAs ("C:\Users\some.name\OneDrive - name\name KPI Reporting\PPT\" & filnam & " " & filnam2 & ".ppt")

End Sub