Runtime Error for Chart Set Source Data in VBA while using Range.Address-CodePudding

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!

CodePudding user response：

It might be easier to just use Union here:

UBMainChart.SetSourceData Source:=Union(ChartRangeTitles, ChartRange), PlotBy:=xlColumns