Macro to create a looping macro to populate another worksheet.

Hi sungen99

Would you WISH to choose the End Date? Currently the Code defaults to the last available date.

Here's the Workbook modified to allow you to choose the Start Date AND the End Date.

Hi sungen99

I've annotated the Code...let me know what you do not understand.

Option Explicit
Sub Rectangle1_Click()
   Dim ws           As Worksheet
   Dim ws1          As Worksheet
   Dim ws2          As Worksheet
   Dim LR           As Long
   Dim NR           As Long
   Dim StartRow     As Long
   Dim Rng          As Range
   Dim cel          As Range

   Set ws = Sheets("Settlement Prices")
   Set ws1 = Sheets("Output")
   Set ws2 = Sheets("Inputs")

   If ws2.Cells(1, "B").Value > ws2.Cells(2, "B").Value Then
      MsgBox "Start Date is Greater than End Date"
      Exit Sub
   End If


   'Clear Output Sheet except for headers
   With ws1 'Sheets Output
      .UsedRange.Offset(2, 0).ClearContents
      NR = 3
   End With

   With ws  'Sheets Settlement Prices
      LR = .Range("A" & .Rows.Count).End(xlUp).Row

      Set Rng = .Range("A4:A" & LR)

      'Search for the Start Date in Settlement Prices Column A
      'and capture it's Row Number (StartRow)
      StartRow = Rng.Find(ws2.Cells(1, "B").Text, , xlValues, xlWhole, xlByRows, xlNext, False).Row
      For Each cel In .Range(.Cells(StartRow, "A"), .Cells(LR, "A"))
         Application.EnableEvents = False
         
         'Copy that Date (cel.text) to inputs B4
         ws2.Cells(4, "B").Value = cel.Text
         
         'Copy that Date (cel.Value) to Outputs K2
         ws1.Range("K2").Value = cel.Value
         
         'Copy that Date (cel.Value) to Outputs L2
         ws1.Range("L2").Value = cel.Offset(0, 13).Value
         
         'Copy that Date (cel.Value) to Outputs M2
         ws1.Range("M2").Value = cel.Offset(0, 8).Value
         
         'Copy Outputs Range("K2:M2").Value to the next available row
         'in Outputs Colimn A
         ws1.Cells(NR, "A").Resize(1, 3).Value = ws1.Range("K2:M2").Value
         NR = NR + 1
         Application.EnableEvents = True
         
      'Move on to the next Cell In .Range(.Cells(StartRow, "A"), .Cells(LR, "A"))
      'and repeat the process until the last row
      Next cel
   End With
End Sub