0

I'm trying to put the data source from one sheet to the Chart in the other sheet. I'm able to achieve the data collection, but at the end, when we set the Source Data, it is throwing Run time error 1004 - Method 'Range' of object'_Global' failed.

Following is the code I'm using

Sub UBCharts()

Set Wb = ThisWorkbook
Set WsCharts = Wb.Sheets("Trend Charts")
Set UBMainChart = WsCharts.ChartObjects("UBMainChart")
Set UBMonthlyYTDSht = Wb.Worksheets("UM - Monthly & YTD Trend")
YearValue = WsCharts.Range("A1").Value
'LookupDate = CDate("" & "01/01/" & YearValue & "")

Xrows = UBMonthlyYTDSht.Range("A" & Rows.Count).End(xlUp).Row
MatchStartRow = Application.WorksheetFunction.Match(CLng(CDate(DateSerial(YearValue, 1, 1))), UBMonthlyYTDSht.Columns("A:A"), 0)
MatchEndRow = Application.WorksheetFunction.Match(CLng(CDate(DateSerial(YearValue, Month(CLng((DateAdd("m", -1, Date)))), 1))), UBMonthlyYTDSht.Columns("A:A"), 0)
UBMainChart.Activate

'ActiveChart.SetSourceData Source:=Range("'UM - Monthly & YTD Trend'!$A$1:$L$1,'UM - Monthly & YTD Trend'!$A$14:$L$23")
UBMainChart.Chart.ChartArea.ClearContents
On Error Resume Next
UBMonthlyYTDSht.Activate
Set ChartRange = UBMonthlyYTDSht.Range(Cells(MatchStartRow, 1), Cells(MatchEndRow, Lcols))
Set ChartRngTitles = UBMonthlyYTDSht.Range(Cells(1, 1), Cells(1, Lcols))
On Error GoTo 0

UBMainChart.Activate
RngStr = """'" & UBMonthlyYTDSht.Name & "'!" & ChartRngTitles.Address & "," & "'" & UBMonthlyYTDSht.Name & "'!" & ChartRange.Address & """"
ActiveChart.SetSourceData Source:=Range(RngStr), PlotBy:=xlColumns 'I'm getting error here
'"'UM - Monthly & YTD Trend'!$A$1:$L$1,'UM - Monthly & YTD Trend'!$A$14:$L$23"

End Sub

Appreciate your help!

mrk777
  • 117
  • 1
  • 12
  • Side note: get rid of the `On Error Resume Next`, `UBMonthlyYTDSht.Activate`, and `On Error GoTo 0`, and put `UBMonthlyYTDSht.` before the inner `Cells` calls, see [this](https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells) or [this](https://stackoverflow.com/questions/8047943/excel-vba-getting-range-from-an-inactive-sheet) for an explanation. – BigBen Nov 09 '21 at 18:00
  • I just did that!! but I'm encountering the same error with the last but one coded line. `ActiveChart.SetSourceData Source:=Range(RngStr), PlotBy:=xlColumns` – mrk777 Nov 09 '21 at 18:08

1 Answers1

2

It might be easier to just use Union here:

UBMainChart.SetSourceData Source:=Union(ChartRangeTitles, ChartRange), PlotBy:=xlColumns
BigBen
  • 46,229
  • 7
  • 24
  • 40