I've figured it out. It started with needing to set up my data with two defined series, where one series is the group of interest and the second is the peer groups and then to conditionally populate cells in my data table based on the group. The set-up took a lot of time to figure out and looks exhaustive in the instructions below, but it's actually not that bad.
Everything highlighted in blue is a formula, so here's how I did it:
- In column B, I used a formula to identify which series is which: =IF(A5<>A$1,"Peers","Group")
- Entered my X and Y values (columns C and D).
- Set my bubble size (column E) to auto-adjust based on the series: =IF(A5=A$1,1000,250)
- Used columns F and G to trim decimal places.
- Columns H through L reflect the actual data series set-up based on whether the group is a peer or the group of interest.
- The X's will always remain the same, so I linked column H to the original X-axis value: =C5
- Column I pulls the appropriate Y value if it's a peer group: =IF($B5=$I3,$D5,NA())
- Column J sets the peer group bubble size: =IF(ISNA($I5),NA(),$E5)
- Column K pulls the appropriate Y value if it's the group of interest: =IF($B5=$K$3,$D5,NA())
- Column L sets the group of interest bubble size: =IF(ISNA($K5),NA(),$E5)
- Column M sets all bubble sizes equal and is used for the final chart creation where all groups and their logos are displayed together.
- Cells O4 (=VLOOKUP(A1,Logos!A:B,2,FALSE)) and P4 (=IF(O4=1,"group1",IF(O4=2,"group2",IF(O4=3,"group3",IF(O4=4,"group4"))))) identify and pull in the correct logo when the group of interest is selected.
I then had to set up and link in all my logos/images on a separate tab ("Logos"). Oz du Soleil's YouTube video on "Select an image based on a cell value" was immensely helpful in getting this done. I don't have enough reputation to post the direct link.
I created and formatted my bubble charts then selected the data as needed for each series using columns H (X-axis), I/K (Y-axis), and J/L (Z-axis for bubble size).
For my full group logo chart, I needed only one series because all bubbles would be the same size. I used columns C, D, and M for the X, Y, and Z-axes, respectively. I then applied each group logo to their respective bubbles.
The code itself ended up being pretty straight-forward after the setup:
Dim i As Long
Dim iLastRow As Long
Dim Cells As Range
'This section looks up each group name on the tab and cycles through the loop
Application.ScreenUpdating = False
With ActiveSheet
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 4 To iLastRow - 1 'Group names start in A5
'Selects, copies, and pastes the next group name in the list
Sheets("Groups").Select
ActiveSheet.Cells(i + 1, 1).Copy
Range("A1").Select
Selection.PasteSpecial paste:=xlPasteValues
'Selects, copies, and pastes the group logo onto the bubble
ActiveSheet.Shapes.Range(Array("Picture 7")).Select
Selection.CopyPicture xlScreen, xlPicture
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.FullSeriesCollection(2).Select
Selection.paste
'Variables needed for directory and file names
GroupName = Sheets("Groups").Range("A1")
yearmo = Sheets("Groups").Range("A2")
'Will create a new folder for the final images if it doesn't already exist
If Len(Dir("DirPath\" & yearmo, vbDirectory)) = 0 Then
MkDir "DirPath\" & yearmo
End If
'Set up image file names
Dim NewFileName As String
NewFileName = "\" & yearmo & " - " & GroupName & " - X_Y.jpg"
'Selects and saves the bubble chart as a JPG
ActiveChart.ChartArea.Select
ActiveChart.Export "DirPath\" & yearmo & NewFileName
Next i
End With
'After all individual bubble have been created, need to
'set up state file name to generate image with all logos for the state
Dim StateFileName As String
StateFileName = "\" & yearmo & " - STATE - X_Y.jpg"
'Selects and saves the state bubble chart as a JPG
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.Export "DirPath\" & yearmo & StateFileName
I opted against using labels in the chart because trying to get them to position dynamically didn't seem worth the effort. I instead went with creating linked text boxes below the chart.
Hopefully this is of some help to others; it's certainly a massive time saver from manual updates.