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