VBA Make the data in another sheet the reference range of the graph-CodePudding

I've recently started using VBA, so I'm sorry for the rudimentary question.

When creating a bar graph, I want to get the reference range of the data from another sheet.

However, when I refer to another sheet, error 1004 appears in the specified part.

The book has two sheets, View and Data Sheet.

A graph is displayed in View. The Data Sheet has data to use for the graph.

I'm trying to write the following code to dynamically update the graph.

Sub Button_Click()

Dim endRowNo As Long

    endRowNo = GetEndRowCountNo()
    SortDate (endRowNo)
    GraphUpdate (endRowNo)
End Sub

Function GetEndRowCountNo() As Long

    Worksheets("DataSheet").Activate

    Dim endRowNo As Long

    For endRowNo = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
        If Cells(endRowNo, "A") <> "" Then Exit For
    Next endRowNo

    GetEndRowCountNo = endRowNo

End Function

Sub SortDate(endRowNo As Long)

    Worksheets("DataSheet").Activate

    ActiveSheet.Sort.SortFields.Clear

    ActiveSheet.Sort.SortFields.Add _
        Key:=ActiveSheet.Cells(1, 1), _
        SortOn:=xlSortOnValues, _
        Order:=xlAscending, _
        DataOption:=xlSortNormal
    
    With ActiveSheet.Sort
        .SetRange Range(Cells(1, 1), Cells(endRowNo, 7))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Sub GraphUpdate(endRowNo As Long)

Worksheets("View").Activate

ActiveSheet.ChartObjects("BarChart").Activate

ActiveChart.SeriesCollection(1).Name = "ValueA"
ActiveChart.SeriesCollection(1).XValues = Worksheets("DataSheet").Range(Cells(2, 5), Cells(endRowNo, 5))
ActiveChart.SeriesCollection(1).Values = Worksheets("DataSheet").Range(Cells(2, 6), Cells(endRowNo, 6))

ActiveChart.SeriesCollection(2).Name = "ValueB"
ActiveChart.SeriesCollection(2).XValues = Worksheets("DataSheet").Range(Cells(2, 5), Cells(endRowNo, 5))
ActiveChart.SeriesCollection(2).Values = Worksheets("DataSheet").Range(Cells(2, 7), Cells(endRowNo, 7))

End Sub

I get an error in the ActiveChart.SeriesCollection (1) .XValues = Worksheets ("DataSheet"). Range (Cells (2, 5), Cells (endRowNo, 5)) part in GraphUpdate. What's wrong with this? Please give me advice.

Sub GraphUpdate(endRowNo As Long)

    Worksheets("View").Activate

    ActiveSheet.ChartObjects("BarChart").Activate

    ActiveChart.SeriesCollection(1).Name = "ValueA"
    ActiveChart.SeriesCollection(1).XValues = Worksheets("DataSheet").Range(Cells(2, 5), Cells(endRowNo, 5))
    ActiveChart.SeriesCollection(1).Values = Worksheets("DataSheet").Range(Cells(2, 6), Cells(endRowNo, 6))

    ActiveChart.SeriesCollection(2).Name = "ValueB"
    ActiveChart.SeriesCollection(2).XValues = Worksheets("DataSheet").Range(Cells(2, 5), Cells(endRowNo, 5))
    ActiveChart.SeriesCollection(2).Values = Worksheets("DataSheet").Range(Cells(2, 7), Cells(endRowNo, 7))

End Sub

※DataSheet

CodePudding user response：

so the issue was:

Worksheets("DataSheet").Range(Cells(2, 5), Cells(endRowNo, 5))

when you use cells within a range like that they need the reference to the worksheet as well i.e.

Worksheets("DataSheet").Range(Worksheets("DataSheet").Cells(2, 5), Worksheets("DataSheet").Cells(endRowNo, 5))

however, this quickly gets ugly so the below code is a little nicer:

Sub GraphUpdate(endRowNo As Long)

    Dim wsView, wsData As Worksheet, cht As ChartObject
    
    Set wsView = Worksheets("View")
    Set wsData = Worksheets("DataSheet")
    Set cht = wsView.ChartObjects("BarChart")
    
    With cht.Chart
        .SeriesCollection(1).Name = "ValueA"
        .SeriesCollection(1).XValues = wsData.Range(wsData.Cells(2, 5), wsData.Cells(endRowNo, 5))
        .SeriesCollection(1).Values = wsData.Range(wsData.Cells(2, 6), wsData.Cells(endRowNo, 6))
        .SeriesCollection(2).Name = "ValueB"
        .SeriesCollection(2).XValues = wsData.Range(wsData.Cells(2, 5), wsData.Cells(endRowNo, 5))
        .SeriesCollection(2).Values = wsData.Range(wsData.Cells(2, 7), wsData.Cells(endRowNo, 7))
    End With
    
End Sub

let me know how you get on and please accept the answer if it works