Macro Assistance

jk11111

New Member
Joined
Sep 27, 2018
Messages
5
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>
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
What are the exact ranges your criteria and company/product are in?
Is nothing going in column A on the destination tab?
 
Upvote 0
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.
 
Upvote 0
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.
Code:
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
 
Upvote 0
Hello,

I have created the following:

Code:
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
 
Upvote 0
You are welcome.
And welcome to the Board!:)
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top