Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement