Creating Charts using VBA and Sub Routines

Question

I am creating charts using data from a tabs called Team1-Team8. I am creating the charts for each team ok but I can't get the charts into each team tab called "Team a - Charts". Below is the code i have so far for just Team A.My Parameter sheet, Column B has the names of the tabs for the charts and Column A is the Team names. Any pointers would help.

Sub LooproutineCharts()

Dim TeamName As String
Dim TeamNameCharts As String

For i = 4 To 12

TeamName = Sheets("Parameter").Range("A" & i).Value 'identify the location

TeamNameCharts = Sheets("Parameter").Range("B" & i).Value 'identify the location

Call Charts(TeamName) ' Call subroutine

Call Charts(TeamNameCharts) ' Call subroutine

Next i

End Sub

Sub Charts(TeamName As String)

'Create a Line Chart for Healthy Start Docu'

Dim lastRow As Long
Dim ws As Worksheet
Set ws = Sheets(TeamName)

 With Sheets(TeamName)
    lastRow = .Range("U" & Rows.count).End(xlUp).Row
With ws
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.Parent.Name = "Variable A"
    ActiveChart.SetSourceData Source:=.Range("S3:U" & lastRow)

ActiveSheet.Shapes("Variable A").Top = 20
ActiveSheet.Shapes("Variable A").Left = 20
ActiveSheet.Shapes("Variable A").Height = 300
ActiveSheet.Shapes("Variable A").Width = 700

ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = "Variable A" TeamName"



    End With
    End With

    End Sub

in your Charts Sub within the "with ws", you are referencing the activesheet, which may well be different from the ws, so as a first measure you could try to remove the activesheet reference, so it just reads ".Shapes.addChart.Select" — Andreas N.
– Andreas N., Commented Jun 10, 2016 at 10:26
thanks for the reply. I get a read only property error on the line "Parent.Name = "Variable A"" — user3018495
– user3018495, Commented Jun 10, 2016 at 10:42
You are also using it in the ChartTitle (though the syntax is off), but is the "Variable A" intended for use other than referencing the shape? — Andreas N.
– Andreas N., Commented Jun 10, 2016 at 11:07

Andreas N. · Accepted Answer · 2016-06-10 12:10:37Z

2

I would suggest updating the Charts Sub to make use of Worksheet.ChartObjects

Using the ChartObject, you can set it and won't have to reference a shape by name. It would look like this:

Sub Charts(TeamName As String)
    'Create a Line Chart for Healthy Start Docu'
    Dim theChart As ChartObject
    Dim lastRow As Long
    Dim ws As Worksheet
    Set ws = Sheets(TeamName)

    With ws
        lastRow = .Range("U" & Rows.Count).End(xlUp).Row

        Set theChart = .ChartObjects.Add(Left:=20, Top:=20, Width:=700, Height:=300)
        With theChart.Chart
            .ChartType = xlLineMarkers
            .SeriesCollection.Add Source:=ws.Range("S3:U" & lastRow)
            '.SeriesCollection(1).XValues = ws.Range("S2:U2") 'I have no idea where your xaxis is placed, or if it exist
            .HasTitle = True
            .ChartTitle.Text = TeamName
        End With
    End With

End Sub

I've taken the liberty to assume that the chart title should match the TeamName argument. I've also made it ready for the xAxis, but I have no oidea if it is relevant, or where it is placed

edited Jun 10, 2016 at 12:10

answered Jun 10, 2016 at 12:00

Andreas N.

3461 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3018495 Over a year ago

That has placed the charts in to the correct tabs! thanks!

user3018495 Over a year ago

Why is the question -1?

Collectives™ on Stack Overflow

Creating Charts using VBA and Sub Routines

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related