Macro Assistance

[jk11111]

Hello!
Ok; I've never created a macro before and would really appreciate some help.
I am trying to create a spreadsheet that will populate data based on a few criteria so that I do not have to constantly type everything out each and every time. On one tab, I want to have a start date and an end date. I also will have a list of companies in one column and products in the column next to it. I would like to have a macro create a row for each Company-Product for every date between the start date and end date (including those dates). I would like the data to have the Date in Column B, Company in Column C, and Product listed in Column D.

Please see the sample tables below. Any assistance is greatly appreciated and do not hesitate to reach out for any clarification!

Criteria

Start Date:	12/01/18
End Date:	12/03/18

<tbody>
</tbody>

Company	Product
Lay’s	Potato Chips
Wise	Potato Chips
Utz	Thin Pretzels
Hershey’s	Kisses
Hershey’s	Kisses with Almonds

<tbody>
</tbody>

Result:

Date	Company	Product
12/01/18	Lay’s	Potato Chips
12/02/18	Lay’s	Potato Chips
12/03/18	Lay’s	Potato Chips
12/01/18	Wise	Potato Chips
12/02/18	Wise	Potato Chips
12/03/18	Wise	Potato Chips
12/01/18	Utz	Thin Pretzels
12/02/18	Utz	Thin Pretzels
12/03/18	Utz	Thin Pretzels
12/01/18	Hershey’s	Kisses
12/02/18	Hershey’s	Kisses
12/03/18	Hershey’s	Kisses
12/01/18	Hershey’s	Kisses with Almonds
12/02/18	Hershey’s	Kisses with Almonds
12/03/18	Hershey’s	Kisses with Almonds

<tbody>
</tbody>

 

[Joe4]

What are the exact ranges your criteria and company/product are in?
Is nothing going in column A on the destination tab?

 

[jk11111]

Column A would be a concatenate of the Company, Product, Date, ie: Hershey’s_Kisses_12/02/18
For the criteria, whatever columns are easiest for you to help me with, I can work with. Though the criteria can be thousands of rows.

 

[Joe4]

OK. I am assuming that the start date is in cell B2 and the end date is in cell B3.
And your company/product data is in columns A and B and starts on row 6.

Here is code that will do what you want. I documented it so you can follow along to see what it is doing, and can easily change values (like sheet names and range values) to get it to work for you.

Sub MyCopyMacro()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim dtStart As Date
    Dim dtEnd As Date
    Dim fr As Long
    Dim lr As Long
    Dim r As Long
    Dim i As Long
    Dim dte As Date
    Dim cmp As String
    Dim prd As String
    
    Application.ScreenUpdating = False
    
'   Set sheet name
    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")
    
'   Capture start/end dates
    dtStart = ws1.Range("B2")
    dtEnd = ws1.Range("B3")
    
'   Set start row of companies and find ending row
    fr = 6
    lr = ws1.Cells(Rows.Count, "A").End(xlUp).Row
    
'   Put headings on destination sheet
    ws2.Range("A1") = "Date"
    ws2.Range("B1") = "Company"
    ws2.Range("C1") = "Product"
    
'   Set initial starting row value on destination sheet
    r = 2
    
'   Make sure end date is after start date
    If dtStart > dtEnd Then
        MsgBox "The end date must be after the start date!"
        Exit Sub
    End If
    
'   Loop through all data
    For i = fr To lr
'       Capture values from current row
        cmp = ws1.Cells(i, "A")
        prd = ws1.Cells(i, "B")
'       Loop through all dates
        For dte = dtStart To dtEnd
'           Assign values to destination sheet
            ws2.Cells(r, "A") = cmp & "_" & prd & "_" & Format(dte, "mm/dd/yy")
            ws2.Cells(r, "B") = dte
            ws2.Cells(r, "C") = cmp
            ws2.Cells(r, "D") = prd
'           Increment row number
            r = r + 1
        Next dte
    Next i
    
    Application.ScreenUpdating = True

    MsgBox "Process complete!"

End Sub

 

[ldonkers]

Hello,

I have created the following:

Option Explicit






Public Sub FillDates()


    Dim StartDate As Date
    Dim EndDate As Date
    Dim NextDay As Date
    
    
    Dim DataStart As Range
    Dim NewData As Range
    
    StartDate = Range("B1").Value
    EndDate = Range("B2").Value
    
    NextDay = StartDate
    


   
   Set DataStart = Range("A6")
   Set NewData = Range("A16")


    
    
    Do
          
        Do


            
            NewData.Value = NextDay
            Set NewData = NewData.Offset(0, 1)
            NewData = DataStart.Value
            
            Set NewData = NewData.Offset(0, 1)
            NewData = DataStart.Offset(0, 1).Value
            
            
            Set NewData = NewData.Offset(1, -2)
            
            Set DataStart = DataStart.Offset(1, 0)
        
            
        Loop Until IsEmpty(DataStart) = True
          
            Set DataStart = Range("A6")
           NextDay = NextDay + 1
    
    Loop Until NextDay > EndDate
    






End Sub

This would all be done on sheet1. You can modify as necessary.

Start Date is B1
End Date is B2

Data table starts in A6

Completed Data Starts in A16

 

[jk11111]

Thank you both sooo much! You are such a life saver!

 

[Joe4]

You are welcome.
And welcome to the Board!