Adding chart to multiple sheets

[riley454]

I have about 50 sheets that follow a specific template of formulas, formatting etc. Each sheet is for an individual name with their own profit/loss blah blah. I want to add a line chart to each sheet using certain ranges of data, to easily scroll through sheets and have a quick visual display of performance.

I don't have much practice with charts but adding a chart to a single sheet showing the information I need is easy enough however I am unsure the most efficient way to have a cookie cutter approach to this for 50 sheets.

Excel doesn't seem to allow inserting the same chart setup to multiple sheets in the same way you can modify other things by simply selecting multiple sheets

When copying the chart and pasting to another sheet it keeps the same source data range from the original sheet rather than updating it with the same ranges of the sheet it was pasted into

Am I missing something obvious here it this a limitation of excel that requires manual or VBA intervention?

 

[NdNoviceHlp]

You didn't indicate where your chart data is? Save a copy of your wb and then trial this code. U will need to adjust the code to reflect where the data is located. It currently charts 2 series from A1 to B & last row of data for all sheets in the wb. HTH. Dave

Sub test()
Dim Sht As Worksheet, YValue As Range, Y2Value As Range
Dim ChartRange As Range, LastRow As Integer
For Each Sht In ThisWorkbook.Sheets
'chart data in "A" & "B" in all sheets
LastRow = Sht.Range("A" & Sht.Rows.Count).End(xlUp).Row
Set YValue = Sht.Cells(1, 1) '1st series
Set Y2Value = Sht.Cells(LastRow, 2) 'last series
Set ChartRange = Sht.Range(YValue, Y2Value)
Call ChartSht(Sht, ChartRange)
Next Sht
End Sub

Public Sub ChartSht(Sht As Worksheet, ChrtRng As Range)
'make chart
With Sht
Charts.Add
End With
ActiveChart.Location Where:=xlLocationAsObject, Name:=Sht.Name
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=ChrtRng, PlotBy:=xlColumns
End Sub

ps. To operate, run the test sub. To run the code a second time, some extra code to remove any existing chart on each sheet is required but depends on what if any other charts and/or shapes U may have on each sheet.

 

[riley454]

Thanks for your reply ND. You answered my main question that this requires a VBA intervention due the the limitations of excel.

I initially didn't include many specifics of my requirements, so didn't test your macro yet until I got my head around using VBA with charts.

I attempted a "record macro" and got the following code. This works OK running on a different sheet but if I run it on the same sheet a second time it gets all messed up on the data ranges even after I have removed the (visible) chart that was previously created. Perhaps excel still 'sees' the previously deleted chart even though I don't?!?

Sub addChart()
    ActiveSheet.Shapes.addChart.Select
    ActiveChart.ChartType = xlLine
    ActiveChart.SeriesCollection(1).Values = "='dataset1'!$E$35:$BM$35"
    ActiveChart.SeriesCollection(1).XValues = "='dataset1'!$E$31:$BM$31"
End Sub

My initial thought was to wrap that in a for/next loop and replace 'dataset1' with each sheetname as a variable but I can't even run the same code snippet from scratch on the same sheet.

 

[Akbarov]

Something like this? change ranges for your need.

Sub AddCharts()
Dim ws As Worksheet
Dim sh As Worksheet
Dim CHARTDATA As Range
For Each ws In ThisWorkbook.Worksheets

    Set sh = ws
   
    sh.Activate
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLine
    Set CHARTDATA = sh.Range("E35:BM36")
    ActiveChart.SetSourceData Source:=CHARTDATA, PlotBy:=xlColumns
    ActiveChart.SeriesCollection(1).Values = "='" & sh.Name & "'!" & "$E$35:$BM$35"
    ActiveChart.SeriesCollection(1).XValues = "='" & sh.Name & "'!" & "$E$31:$BM$31"
Next ws

End Sub