Dynamic Date Range When Charting with VBA

[plaroche2]

I am using VBA code taken from the Mr. Excel book, "VBA and Macros for MS Excel" I have the charting code working correctly when it is referencing static ranges of data. In my case the previous 12 months of data and the current year data respectively in two different macros. I would like to create a rolling twelve months chart that references the current month and charts the previous 12 months crossing over fiscal years.

The code is looping through rows of a large excel file data set and charting data in a range that consists of monthly data. Can this code be modified to select the range dynamically based on the current month?
The range I am referring to in the code is in the SeriesCollection() XValues and Values.

The nuts and bolts of the code is below.
There is plenty more lines of code which deal with formatting and setting up the pages to be printed.

Thanks in advance.

 'finding the last row in the range and setting varialble LastRow to that number
    LastRow = WS.Range("B65536").End(xlUp).Row
    For CurrRow = 2 To 25 'LastRow
        Set NewWs = ThisWorkbook.Worksheets.Add
        NewWs.Name = Left(WS.Range("B" & CurrRow).Value, 20)
 
   'adds the chart to the worksheet and formats the chart
 
        Set cht1 = ThisWorkbook.Charts.Add
        With cht1
            .ChartType = xlLineMarkers
            .SeriesCollection.NewSeries
            .SeriesCollection(1).XValues = "=" & NewWs.Name & "!R39C3:R39C14"
            .SeriesCollection(1).Values = "=" & NewWs.Name & "!R40C3:R40C14"
            .SeriesCollection(1).Name = "=""wRVU"""
 
            .SeriesCollection.NewSeries
           .SeriesCollection(2).XValues = "=" & NewWs.Name & "!R39C3:R39C14"
           .SeriesCollection(2).Values = "=" & NewWs.Name & "!R41C3:R41C14"
           .SeriesCollection(2).Name = "=""MGMA TARGET"""
           .SeriesCollection(2).ColorIndex = 57
            .SeriesCollection(2).Weight = xlHairline
            .SeriesCollection(2).LineStyle = xlContinuous
            .SeriesCollection(2).MarkerBackgroundColorIndex = xlAutomatic
            .SeriesCollection(2).MarkerForegroundColorIndex = xlAutomatic
            .SeriesCollection(2).MarkerStyle = xlNone
 
            .Location Where:=xlLocationAsObject, Name:=NewWs.Name
 
             End With
 
'copy and paste into new workbook.
    'the copy method without variants creates a new workbook by default
 
    NewWs.Copy
 
    Set NewWb = ActiveWorkbook
 
    ActiveWorkbook.UpdateLinks = xlUpdateLinksNever
 
 
 
    'saves the new workbook in the same folder as the old workbook
 
         NewWb.SaveAs foldername & NewWs.Name & ".xls"
 
 
         NewWb.Close
 
    'deletes the worksheet in the old workbook without user interaction
 
         Application.DisplayAlerts = False
         NewWs.Delete
         Application.DisplayAlerts = True
 
 
      'onto the next record
 
    Next CurrRow
 
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    'ActiveWindow.WindowState = xlMaximized
    'Application.EnableEvents = True
MsgBox "The Provider Charts Have Been Created in Seperate Excel Workbook Files"
 
End Sub