How to create Chart with source from a different Worksheet

Question

Before coming to ask I searched the web a lot, and the answers I found did not solve the problem. The "Data" Worksheet is in the same Workbook as the "Reports" Worksheet.

This is the part of my code that matters for this question: Private Function CreateChart(ByVal DataRow As Integer, ByVal DataCol As Integer) Dim ChartRowOffset As Integer Charts.Add ActiveChart.ChartType = xlPie ActiveChart.SetSourceData Source:=Sheets("Data").Range(Cells(2, DataCol), Cells(DataRow - 1, DataCol + 1)), PlotBy:=xlColumns ActiveChart.Location Where:=xlLocationAsObject, Name:="Report" ActiveChart.HasTitle = True ActiveChart.ChartTitle.Select ActiveChart.ChartTitle.Text = ThisWorkbook.Sheets("Data").Cells(1, DataCol).Value If DataCol > 1 Then ChartRowOffset = (DataCol - 1) * 2 Else ChartRowOffset = 2 End If With ActiveChart.Parent .Top = ThisWorkbook.Sheets("Report").Cells(ChartRowOffset, 1).Top .Left = ThisWorkbook.Sheets("Report").Cells(ChartRowOffset, 1).Left End With End Function

I am getting error code 1004 "Application-defined or Object-defined error" when I try to select a source from a different worksheet in this line: ActiveChart.SetSourceData Source:=Sheets("Data").Range(Cells(2, DataCol), Cells(DataRow - 1, DataCol + 1)), PlotBy:=xlColumns

If I do it without Sheets("Data"), it gives no error and creates the chart, but it selects cells from the "Reports" sheet, not the Worksheet I need. I tried selecting the "Data" Worksheet, activating it, nothing worked so far.

Any help would be appreciated.

Answer 1

Please try the following to fix the referencing style issue -

ActiveChart.SetSourceData Source:= _
Sheets("Data").Range(Cells(2, DataCol).Address, Cells(DataRow - 1, DataCol + _
1).Address), PlotBy:=xlColumns

The infamous Error: 1004 will haunt you to the death over stuff like this. Also, if I may, your code would be faster, shorter, and easier to read if you used With statements wherever applicable.

Ex:

With ActiveChart
    .Location Where:=xlLocationAsObject, Name:="Report"
    .HasTitle = True
    .ChartTitle.Select
    .ChartTitle.Text = ThisWorkbook.Sheets("Data").Cells(1, DataCol).Value
End WIth

-******UPDATE*******-

After testing, I found I had to re-Set the variable... Seemed crazy to me, however, tested and worked for me:

Private Function CreateChart()

Dim cht As Chart
Dim rng As Range

Set rng = ThisWorkbook.Worksheets("Sheet1").UsedRange
Set cht = Charts.Add

With cht
    .SetSourceData Source:=rng, PlotBy:=xlColumns
    .ChartType = xlPie
    .Location Where:=xlLocationAsObject, Name:="Sheet1"
End With

Set cht = ActiveChart

With cht
    .HasTitle = True
    .ChartTitle.Text = ThisWorkbook.Sheets(1).Name
End With

End Function

Danny,

ExcelVBADude