Need to make a set of graphs in excel using a vba macro loop

Question

I'm trying to make a macro that will run through an excel sheet and go through a set of rows and make a graph for each row.

I've got a bit of code that kind of does what I need, but puts it all on one graph when I need an individual graph for each row.

`Dim i As Integer
 Dim ws As Worksheet
 Set ws = Sheets("Master Sheet")
 For Row = 1 To 20
 Dim my_cell
 Dim rng As Range
 Set rng = Sheets("Master Sheet").Range("J8:Y8")

 For Each my_cell In rng
 If my_cell <> "" Then

 ActiveSheet.Shapes.AddChart.Select
 ActiveChart.SetSourceData Source:=Range("'Master Sheet'!$J$8:$Y$8")
 ActiveChart.ChartType = xlLineMarkers
 ActiveChart.Location Where:=xlLocationAsNewSheet
 ActiveSheet.Activate
 ActiveChart.PlotArea.Select
 ActiveChart.SeriesCollection(1).XValues = "='Master Sheet'!$J$2:$Y$2"
 ActiveChart.SeriesCollection(1).Name = "=""FP"""
 ActiveChart.SeriesCollection.NewSeries
 ActiveChart.SeriesCollection(2).Name = "=""Progress"""
 ActiveChart.SeriesCollection(2).Values = _
 "='Master Sheet'!$J$8,'Master Sheet'!$AF$8:$AH$8"
 ActiveChart.DisplayBlanksAs = xlInterpolated
 ActiveSheet.Activate
 ActiveChart.ChartArea.Select

 Else
 Exit For ' Blank cell found, exiting
 End If
 Next

 Next Row
 End Sub`

If anyone can give me a hand to see where I'm going wrong that would be great.

Community · Accepted Answer · 2017-05-23 11:44:45Z

2

Not really sure you have structured your For Next and For Each loops well. Ideally you'd like to step through ranges and actually use the value you define in your For Each statement. Without seeing your workbook I just adapted a small range of data to simulate creating a graph.

I just took your same code and generate a graph on the same worksheet for each row of numbers. You can take these principles and apply it to your logic.

Sub test()
 Dim Row As Integer
 Dim ws As Worksheet
 Dim rng As Range

 Set ws = Sheets("Sheet1") 'Change this to: Set ws = Sheets("Master Sheet")

 For Row = 1 To 6
 Set rng = ws.Range("B1:D1").Offset(Row, 0) 'Change to (I'm guessing here): ws.Range("$J$7:$Y$7").Offset(Row, 0)

 ActiveSheet.Shapes.AddChart.Select
 ActiveChart.SetSourceData Source:=Range(ws.Name & "!" & rng.Address)
 ActiveChart.ChartType = xlLineMarkers
 ActiveChart.PlotArea.Select
 ActiveChart.SeriesCollection(1).XValues = "='Sheet1'!$B$1:$D$1" 'Change to "='Master Sheet'!$J$2:$Y$2"
 ActiveChart.SeriesCollection(1).Name = ws.Range("A1").Offset(Row, 0).Value 'Change this to whatever you want to name the graphs. This is currently set to dynamicly name each graph by the series name set in Column A.
 'ActiveChart.Location Where:=xlLocationAsNewSheet 'uncomment this line to put on new sheet
 'ws.Select 'Need to go back to worksheet

 Next Row

 Set ws = nothing
 Set rng = nothing
End Sub

Here are a couple links that may help; Creating and positioning graphs in a for loop VBA and Excel VBA: Chart-making macro that will loop through unique name groups and create corresponding charts?

Google also has many other links.

If I've misunderstood your question or need anything else please let me know.

Cheers

edited May 23, 2017 at 11:44

CommunityBot

11 silver badge

answered Oct 16, 2012 at 0:02

Rory

3961 silver badge5 bronze badges

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

2 Comments

user1748459 Over a year ago

Thanks for the help, I've given you code a try and I'm getting an error with this line ActiveChart.SetSourceData Source:=Range(ws.Name & "!" & rng.Address) I'm not sure why, but as it's late it could be me just not seeing it. I think I'll sleep on it and have a look in the morning. Thanks again for the help.

Rory Over a year ago

Have you changed Set ws = Sheets("Sheet1") to your sheet name Set ws = Sheets("Master Sheet"). Sorry I should have done that in the code, but all the range references will need to be changed as well.

Collectives™ on Stack Overflow

Need to make a set of graphs in excel using a vba macro loop

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related