Using range and cell variables in the series function

Hello,

I am trying to automate a report where specific data is pulled out, saved as a range variable and then used to update a number of existing graphs.

I'm having trouble referencing the variables correctly in the code - please could someone help me with this?

Sub Automatic_Reporting(month_value As String, year_value As String)
' Macro building to create automatic reports
' Input year and month to get report

Dim Title As String
Title = Range("A3")

'Search for desired year
    Rows("1:1").Select
    Selection.Find(What:=year_value, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        
'Search for desired month
    Cells.Find(What:=month_value, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Select

'Create the variable for the previous 12 months of data
Dim Twelve_Month_Range As Range
' Move 1 cell down to get to data:
Selection.Offset(1, 0).Select
' Jump to the first month of necessary data - 12 months previously:
Selection.Offset(0, -12).Resize(1, 12).Select
'Save data to range variable
Set Twelve_Month_Range = Selection

'Same as above but for the previous 15 months
Dim Fifteen_Month_Range As Range
Selection.Offset(0, -3).Resize(1, 15).Select
Set Fifteen_Month_Range = Selection

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Select
    Selection.Formula = "=SERIES(Title, Sheet1! & Twelve_Month_Range & , Sheet1! & Twelve_Month_Range & ,1)"

End Sub

It's the final line before 'End Sub' that is causing the problems!!

Any help would be much appreciated.

Thanks,
Rachael

Can you post a copy of the workbook? It will make analysis so much easier.

Try this edit

'Create the variable for the previous 12 months of data
Dim Twelve_Month_Range_X As Range
Dim Twelve_Month_Range_Y As Range

Set Twelve_Month_Range_X = Selection.Offset(0, -12).Resize(1, 12)
' Move 1 cell down to get to data:
Selection.Offset(1, 0).Select
' Jump to the first month of necessary data - 12 months previously:
Selection.Offset(0, -12).Resize(1, 12).Select
'Save data to range variable
Set Twelve_Month_Range_Y = Selection

'Same as above but for the previous 15 months
Dim Fifteen_Month_Range As Range 'Change as above
Selection.Offset(0, -3).Resize(1, 15).Select
Set Fifteen_Month_Range = Selection

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Name = Title
ActiveChart.SeriesCollection(1).Values = "=Sheet1!" & Twelve_Month_Range_Y.Address
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!" & Twelve_Month_Range_X.Address

THANK YOU!!!

You are excellent.

:D