Generating 36 rows from date range

[Marcy]

I built a power automate model to load data from pdf contracts to an excel table. The contract will have a term anywhere from 3 months to 3 years with individual data points for Start date and end date. I would like a way to generate a data set where my product is within 2 of the 6 products. I then want to translate that one row per product into 12 rows with cycle start and end dates representing the months. I would like this to happen automatically or with a refresh when the data loads. Like so:
Input

Product	Start Date	End Date
Widgets	01/01/23	12/31/25
Flippers	03/01/24	05/31/24

Output (dates shown as needed for upload)

Product	Cycle Start	Cycle End
Widgets	010123	013123
Widgets	020123	022823
Widgets	030123	033123
...	...	...
Widgets	120125	123125
Flippers	030124	033124
Flippers	040124	043024
Flippers	050124	053124

I don't have VBA skills.

 

[ranman256]

paste the code below into a module,
start on the sheet with the widgets,
run run macro: BuildList

Sub BuildList()
Dim vProd, vStartDte, vEndDte
Dim vDate As Date, vEoM As Date
Dim wsSrc As Worksheet, wsTarg As Worksheet

Set wsSrc = ActiveSheet
Sheets.Add
Set wsTarg = ActiveSheet
ActiveSheet.Name = "results"

wsSrc.Activate
Range("A2").Select
While ActiveCell.Value <> ""
     vProd = ActiveCell.Value
     vStartDte = ActiveCell.Offset(0, 1).Value
     vEndDte = ActiveCell.Offset(0, 2).Value
    
    wsTarg.Activate
    vDate = vStartDte
    vEoM = DateAdd("d", -1, DateAdd("m", 1, vDate))
    
    While vDate <= vEndDte
       ActiveCell.Offset(0, 0).Value = vProd
       ActiveCell.Offset(0, 1).Value = Format(vDate, "mmddyy")
       ActiveCell.Offset(0, 2).Value = Format(vEoM, "mmddyy")
       
       vDate = DateAdd("m", 1, vDate)
       vEoM = DateAdd("d", -1, DateAdd("m", 1, vDate))
        ActiveCell.Offset(1, 0).Select  'next row
    Wend
       
      'next product
    wsSrc.Activate
    ActiveCell.Offset(1, 0).Select  'next row
Wend

Set wsSrc = Nothing
Set wsTarg = Nothing
End Sub