VBA Drop Down List Copy from workbook to workbook

[Boulder2008]

Hello,
I am currently trying to write a macro that will take a shifts daily output and place it in a daily log that will be progressively kept through the year.

The workbook submitted by my Supervisors has the date, a shift drop down with selections (1ST, 2ND, 3RD) and four different products. Once they fill this out and click on the "Submit" button I would like the macro to copy the relevant data into the correct Shift and Date location in my second workbook. I am not sure how to ensure that the data is copied into the correct shift and date since every day it will need to be indexed to a new spot and we dont always run on the weekend and we dont always run all 3 shifts a day.

I messed around with the "Private Sub Worksheet_Change(ByVal Target As Range) for the drop down list, but had no luck in getting that version of code to work. I appreciate any assistance I can get regarding this issue.

My current code is:

Sub TransferData() Dim WkBk As Workbook Dim SrcWkBk As Workbook Set SrcWkBk = ThisWorkbook Set WkBk = Workbooks.Open(Filename:="INSERT FILE NAME.xlsm") 'Copy Total Number of Parts per Line SrcWkBk.Sheets("Hourly Counts").Range("P4").Copy WkBk.Worksheets("Sheet1").Range("C2").PasteSpecial Paste:=xlPasteValues SrcWkBk.Sheets("Hourly Counts").Range("P6").Copy WkBk.Worksheets("Sheet1").Range("C5").PasteSpecial Paste:=xlPasteValues WkBk.SaveAs ("C:\INSERT FILE LOCATION" & Format(Date, "MM-DD-YYYY") & ".xlsm") 'Clear Clipboard Application.CutCopyMode = False End Sub
My log file looks like: Date Shift 6/27/2018 6/28/2018 6/29/2018 6/30/2018 Product 1 3 1 2 Product 2 3 1 2 Product 3 3 1 2 Product 4 3 1 2 <tbody> </tbody>

<colgroup><col span="2"><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[PhilS2520]

Hello Boulder2008,

Sometimes one will be so involved in their project that they miss a lot of details, that to a new comer the picture is so fuzzy they can’t understand what exactly is needed. This is my case here.

I understand that you want to copy some data from one workbook to another. The way I will do that for simplicity is to write some formulas in the form cell A1 in destination book equals cell A1 in target book. (=targetbook!A$$1).

If I missed the point, please rephrase your question.

 

[Boulder2008]

PhilS2520,
Thank you for your quick response. Maybe I can accomplish my task with your method....I just dont know/understand it. With your method can I do the following:

1) Supervisor opens their daily report and clicks (1st shift/2nd shift/or 3rd shift). They then enter their total number of products produced (Product 1 value = P4, Product 2 value = P6, Product 3 value = P8, Product 4 value = P9)

2) They then submit the form using the button on the sheet and the following is achieved
a. Total Values listed above get transferred to another workbook that is unopened and logged accordingly. Product 1 = C2, Product 2 = C5, Product 3 = C8, and Product 4 = C11 which corresponds to the appropriate Date inside the log.

Then my next Operator comes in and picks (1st shift/2nd shift/or 3rd shift). They enter their total number of products produced (Product 1 value = P4, Product 2 value = P6, Product 3 value = P8, Product 4 value = P9)

They then submit the form using the button on the sheet and the following is achieved
a. Total Values listed above get transferred to another workbook that is unopened and logged accordingly. Product 1 = C3, Product 2 = C6, Product 3 = C9, and Product 4 = C13 which corresponds to the appropriate Date inside the log.

Etc for all 3 shifts....

Then the next day they come in and do it all over again and the submitted totals shift to the appropriate column for the date the parts were ran.

I also forgot to mention that when they submit the form it automatically saves it in a specified location with the Date and Shift in the name as well as generates an email to a pre-determined list of recipients who need to review this data on a daily basis.

Regards,
Boulder2008