Code to change data range for a graph

[TorrO]

Hi

You find my sheet here.

The code is made by searcing net and try to make it work. It don't.... and du to my limitation in understanding debug I dont manage to find the mistake.

Can I humbly ask to get help to make this code work?

Code:

Sub ChangeChartDataSourceByColumnAuto(chartName As String, nextColumn As Boolean)
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim chart As ChartObject
    Set chart = ws.ChartObjects(chartName)
    Dim currentRange As Range
    Set currentRange = chart.chart.SeriesCollection(1).Values
    Dim newRange As Range
    If nextColumn = True Then
        ' Find the last non-empty cell in the next column
        Dim lastRow As Long
        lastRow = ws.Cells(ws.Rows.Count, currentRange.Column + 1).End(xlUp).Row

        ' Define the new data range
        Set newRange = ws.Range(ws.Cells(1, currentRange.Column + 1), _
                                ws.Cells(lastRow, currentRange.Column + 1))
    Else
        ' Find the last non-empty cell in the previous column
        Dim lastRow As Long
        lastRow = ws.Cells(ws.Rows.Count, currentRange.Column - 1).End(xlUp).Row

        ' Define the new data range
        Set newRange = ws.Range(ws.Cells(1, currentRange.Column - 1), _
                                ws.Cells(lastRow, currentRange.Column - 1))
    End If
    chart.chart.SetSourceData newRange
    chart.chart.Refresh
End Sub

Sub CreateNextColumnButton()
    'Dim btn As Button
    'Set btn = ActiveSheet.Buttons.Add(100, 10, 150, 25)
    'btn.Caption = "Next Column"
    btn.OnAction = "ChangeChartDataSourceNextColumn"
End Sub

Sub CreatePreviousColumnButton()
    'Dim btn As Button
    'Set btn = ActiveSheet.Buttons.Add(100, 50, 150, 25)
    'btn.Caption = "Previous Column"
    btn.OnAction = "ChangeChartDataSourcePreviousColumn"
End Sub

Sub ChangeChartDataSourceNextColumn()
    ChangeChartDataSourceByColumnAuto "Chart 1", True
End Sub

Sub ChangeChartDataSourcePreviousColumn()
    ChangeChartDataSourceByColumnAuto "Chart 1", False
End Sub

Br Torro

 

[NdNoviceHlp]

Hi Torro. You can trial this code to set your range (remove your setsourcedata and refresh lines of code). HTH, Dave

ws.ChartObjects(ChartName).Chart.SetSourceData Source:=newRange, PlotBy:=xlColumns