Thread: using vb to create charts over multiple worksheets

More than likely but we would need more information before being able to provide any more.

Have you tried using the macro recorder?

Do you need multiple charts? Perhaps 1 chart with the data filtered by client would suffice.

See attached

This code produces a chart for each client.
The sheet name is based on Policy number for avoid duplicates in names.
If the sheet for a policy exists it will update the chart. If not a new sheet is created based on the ChtTemplate sheet.

that is fantastic... exactly what i needed.

there are 2 other things i'd like aswell on these generated sheets, i wonder if it can be incorporated into the same action.

1. a table of the same data in the chart
2. our company logo in the top left of the sheet.

is this possible at a click of a button too?

EDIT: can you confirm that when i update charts (with the code you provided to me above) it will resize the chart if necessary?

Data is now copied to local sheet and chart uses local data.

You can stick you logo on the chart template sheet.

I have already placed a button on the data sheet.

What do you mean by resize?

The macro updates the sheet, or creates if not already existing, with complete data from Data worksheet.

Yeah that first point is a data distortion so I let you workout how you do that.
If you really don't what you clients to see the trend displayed by the chart why give them a chart just use a table of numbers?

If you format the ChtTemplate sheet then run the macro the newly created sheets should retain the formatting, including page layout.

Code:

Sub CreateClientCharts()

    Dim shtData As Worksheet
    Dim rngData As Range
    Dim shtPolicy As Worksheet
    Dim rngHeader As Range
    Dim rngRow As Range
    Dim rngLabels As Range
    
    On Error Resume Next
    
    ' get Data
    Set shtData = Worksheets("Data")
    Set rngData = shtData.Range("A7").CurrentRegion
    Set rngHeader = rngData.Rows(1)
    Set rngData = rngData.Offset(1, 0).Resize(rngData.Rows.Count - 1, rngData.Columns.Count)
    Set rngLabels = shtData.Range(rngHeader.Cells(1, 12), rngHeader.Cells(1, rngHeader.Columns.Count))
    
    For Each rngRow In rngData.Rows
        ' check whether to create report
        If rngRow.Cells(1, 11).Value = "Y" Then
            Set shtPolicy = m_GetPolicySheet(rngRow.Cells(1, 5).Value)
            
            ' copy over labels and data
            rngLabels.Copy
            shtPolicy.Range("A1").PasteSpecial , , , True
            shtData.Range(rngRow.Cells(1, 12), rngRow.Cells(1, rngRow.Columns.Count)).Copy
            shtPolicy.Range("B1").PasteSpecial , , , True
            
            rngRow.Cells(1, 6).Copy shtPolicy.Range("D32")
            
            With shtPolicy.ChartObjects(1).Chart
                .ChartTitle.Text = Left(rngRow.Cells(1, 2).Value, 1) & " " & rngRow.Cells(1, 1).Value & " " & _
                                   rngRow.Cells(1, 4) & " " & rngRow.Cells(1, 5).Value
                With .SeriesCollection(1)
                    .Values = shtPolicy.Range("B1").Resize(rngLabels.Columns.Count, 1)
                    .XValues = shtPolicy.Range("A1").Resize(rngLabels.Columns.Count, 1)
                    .Name = shtPolicy.Name
                End With
            End With
        End If
    Next
    
End Sub

no worries

The function accepts as it's argument the Policy Number. Previously that was located in column E of the row being processed. Is that still the case?

Also the value in column E is used as the sheet name so should be a unique value and one that is valid as a tab name. No special characters and less the 31 characters.