Excel Userform to Select Start and End Dates and Fill in Table with dates in between

  • by

I created a Userform that takes input of an event, and adds that data to the first blank row in column C of a table.

The userform has inputs for a Start Date and End Date. The code below adds 1 row of data to the table with the start date and the corresponding info.

Private Sub CommandButton1_Click()

Dim LastRow As Long, ws As Worksheet

Set ws = Sheets("2019 Events")

LastRow = ws.Range("C6").End(xlDown).Row   1 ' Finds the first blank row Column C

ws.Range("C" & LastRow).Value = start_date.Value
ws.Range("D" & LastRow).Value = audit_types.Value
ws.Range("E" & LastRow).Value = event_names.Value
ws.Range("G" & LastRow).Value = Site_names.Value
ws.Range("J" & LastRow).Value = names1.Value
ws.Range("K" & LastRow).Value = Names2.Value
ws.Range("M" & LastRow).Value = notes.Value

Unload UserForm1

End Sub

My goal is when a user inputs a Start and End Date, the userform populates a line entry on the table for each date between the start and end, with the rest of the data from the form the same on all rows.

For example, if the Start Date is 12/01/2019 and End Date is 12/03/2019, the userform should add 3 lines of data to the table, where 3 rows in Column C are 12/01/2019, 12/02/2019, and 12/02/2019 and the rest of the rows are duplicates from the other values on the table.

The idea is to populate a list of events and their info, but each date needs it’s own line item even if a single event covers multiple days.

Leave a Reply

Your email address will not be published. Required fields are marked *