1

I want to send a daily distribution of charts in an Excel workbook.

In my master file, the chart ranges are updated each day.
I am sending the charts as pictures to help with easier viewing.

I distribute the charts in 2 columns, hence the modulus operator.
Each chart's size is 25 rows and 13 columns.

Sub ChartPasteValues()

Dim wb As Workbook, aWs As Worksheet, bWs As Worksheet, cht As ChartObject
Dim PasteRow As Integer, count As Integer

Set wb = ThisWorkbook
Set aWs = wb.Worksheets("Charts Master")
Set bWs = wb.Worksheets("Charts Output")

'delete all the prior day's charts in the output worksheet
    bWs.Pictures.Delete

'paste as pictures all of today's charts
    count = 1
    PasteRow = 2
    For Each cht In aWs.ChartObjects
        cht.Chart.ChartArea.Copy
        If count Mod 2 = 1 Then
            bWs.Range("B" & PasteRow).PasteSpecial xlPasteValues
            count = count + 1
        Else
            bWs.Range("M" & PasteRow).PasteSpecial xlPasteValues
            PasteRow = PasteRow + 25
            count = count + 1
        End If
    Next cht
    
End Sub
Community
  • 1
  • 1
PDubs
  • 7
  • 3

0 Answers0