Help! VBA to loop through a list containing data references to update forecast sheets automatically

[RMCHE5280]

I have about ten forecast sheets that need to be updated weekly. Updating the plots is very time-consuming, so I decided to try my hand at a macro that would loop through them to update them automatically. Eventually, I will have the macro delete the old forecast sheets and replace them with the autogenerated sheets. However, I need help figuring out how to generate the forecast sheets automatically using VBA and a loop. While I am not new to VBA, it's also something that I only use a few times a year. For this reason, I'm certain there's a more concise method than the one I have been attempting. Can someone point me in the right direction?

Also, the data in the "hourly results" sheet is really about 10,000 rows long, if that matters, but I can create the forecast sheets manually without trouble... it just takes an hour or so to format everything, which I can also do using VBA if I can only get this part working. Thank you again for your help!

Here is my VBA code:

Sub ForecastGen()

Dim Pgm As Worksheet
Set Pgm = ActiveWorkbook.Sheets("Programming")

Dim DtaRng As Worksheet
Set DtaRng = ActiveWorkbook.Sheets("Hourly Results")

Dim DateLocation As Range
Set DateLocation = Pgm.Range("G2")
MsgBox DateLocation

Dim Location(1 To 10) As Range
Dim k As Integer

For k = 1 To 10
Set Location(k) = Pgm.Cells(k + 1, 6)
MsgBox Location(k)

    ActiveWorkbook.CreateForecastSheet Timeline:=Sheets(DtaRng).Range(DateLocation _
        ), Values:=Sheets(DtaRng).Range(Location), ForecastEnd:= _
        "4/1/2024  12:00:00 AM", ConfInt:=0.95, Seasonality:=1, ChartType:= _
        xlForecastChartTypeLine, Aggregation:=xlForecastAggregationAverage, _
        DataCompletion:=xlForecastDataCompletionInterpolate, ShowStatsTable:=False
       
Next k
   
End Sub

Mini-sheet upload did not work for me, so I am trying to get my data in the rudimentary way.

Here is an example of the information in the "Programming" sheet:

ID	Plot Name	Column	Start	End	DataRange	DateRange
1​	Plot 1	B	2​	200​	B2:B200	A2:A200
2​	Plot 2	C	2​	200​	C2:C200	A2:A200
3​	Plot 3	D	2​	200​	D2:D200	A2:A200
4​	Plot 4	E	2​	200​	E2:E200	A2:A200
5​	Plot 5	F	2​	200​	F2:F200	A2:A200
6​	Plot 6	G	2​	200​	G2:G200	A2:A200
7​	Plot 7	H	2​	200​	H2:H200	A2:A200
8​	Plot 8	I	2​	200​	I2:I200	A2:A200
9​	Plot 9	J	2​	200​	J2:J200	A2:A200
10​	Plot 10	K	2​	200​	K2:K200	A2:A200

Here is an example of the data in the "Hourly Results" sheet:

Date	Plot 1	Plot 2	Plot 3	Plot 4	Plot 5	Plot 6	Plot 7	Plot 8	Plot 9	Plot 10
1/1/24 0:00​	2.042377​	2.04958​	2.050379​	2.047442​	2.047692​	2.054683​	2.053042​	2.033847​	2.039204​	2.208346​
1/1/24 1:00​	2.042376​	2.049579​	2.05038​	2.047443​	2.047692​	2.054683​	2.053045​	2.033847​	2.039204​	2.208343​
1/1/24 2:00​	2.042376​	2.049578​	2.050381​	2.047444​	2.047693​	2.054684​	2.053047​	2.033846​	2.039204​	2.208338​
1/1/24 3:00​	2.042376​	2.049577​	2.050382​	2.047444​	2.047693​	2.054684​	2.053049​	2.033846​	2.039203​	2.208334​

 

[Domenic]

Try the following...

    ActiveWorkbook.CreateForecastSheet _
        Timeline:=DtaRng.Range(DateLocation.Value), _
        Values:=DtaRng.Range(Location(k)), _
        ForecastEnd:="4/1/2024  12:00:00 AM", _
        ConfInt:=0.95, _
        Seasonality:=1, _
        ChartType:=xlForecastChartTypeLine, _
        Aggregation:=xlForecastAggregationAverage, _
        DataCompletion:=xlForecastDataCompletionInterpolate, _
        ShowStatsTable:=False

By the way, there's no need to use a "Programming" sheet. You can achieve the same results without it. Or is there some reason for using it?

Hope this helps!

 

[RMCHE5280]

Domenic: Thank you for your help. I'll try your code shortly. My thought with the programming sheet is that the dataset size is dynamic—it's continually growing. Also, the data in the "hourly results" table contains some blank cells. When I attempted to specify the last active cell using VBA in my earlier attempts, the blank cells would throw it off, and the entire dataset would not be included in the forecast sheet. Just for my own education, what more eloquent solution should I have used in place of the "programming" sheet? Thanks again!

 

[Domenic]

A dynamic range could easily be dealt with. However, as you already know, those blank cells will cause an error.