VBA transferring data

[Douglas Schafer]

Hello, 

I have been trying to create an inventory tracker in excel utilizing VBA. I have an excel sheet currently being used by my employees that lists out the total number products made during an 8hr shift that they submit to me. I then have to go into another workbook and manually type in the totals, the date, and the shift. I would like to make this be completed by them just filling out the excel sheet and hitting a submit button that will automatically save the workbook in a location with the name being Date and Shift, as well as log the totals into my Log book excel sheet. The difficulty I am finding is I need the macro to look at the selected shift from the drop down menu and the date and know where to copy it to and I need it done for 1st,2nd, & 3rd each day. So far I have been able to get the copy feature done, but have had no luck with incorporating the shift and date. I am hoping somebody might be able to point me into the write direction? I appreciate your assistance. 

Sub TransferData()
'Copy Total Number of Parts per Line
Sheets("Hourly Counts").Range("P4,P6,P8:P9").Copy

Workbooks.Open Filename:= _"file location"
Worksheets("Sheet1").Range("D2").PasteSpecial Paste:=xlPasteValues
'Clear Clipboard
Application.CutCopyMode = False

End Sub

[#2]

Try something like this:

Sub TransferData()

 Dim WkBk As Workbook
 Dim SrcWkBk As Workbook
 
 Set SrcWkBk = ThisWorkbook
 'Copy Total Number of Parts per Line
 SrcWkBk.Sheets("Hourly Counts").Range("P4,P6,P8:P9").Copy
 
 Set WkBk = Workbooks.Open(Filename:="file location")
 WkBk.Worksheets("Sheet1").Range("D2").PasteSpecial Paste:=xlPasteValues
 
 'In this example the date is the current Date and the Shift would be in Cell A1 of the
 'wkbook the code resides in....
 WkBk.SaveAs "file location" & "_" & Format(Date, "MM-DD-YYYY") & "_" & SrcWkBk.Range("A2") & ".xlsx"
 'Clear Clipboard
 Application.CutCopyMode = False

End Sub

[#3]

Matt,

Thank you for this code. I have a couple of questions regarding an improvement. 

1)Is is possible to copy multiple cells and paste them into anther workbook but not be copied into the same column? I have data points I need to copy from P4,P6,P8, and P9 in one workbook and then I need to paste them into the corresponding cells based off the product line and shift. For example cell P4 is for 3rd shift of product 1 so I need to paste its value in C2. P6 is 3rd shift total for product 2 so its value needs to be pasted in C5....etc. I have attached an excel sheet of the data base format I am need to use. Is this possible? 

2)Is there a way to have the data be pasted into the correct date based off the date that is inputted in the first workbook? For example today is 6/26/18 and I produced 300 parts of product 1 and 200 parts of product 2 and when I hit the submit button it gets logged in the second workbook in the appropriate date/shift?  

I appreciate your assistance on this. 

[#4]

Hey Douglas-

Please see a few amendments to the code based on your questions:

Sub TransferData()
    
    Dim WkBk As Workbook
    Dim SrcWkBk As Workbook
    
    Set SrcWkBk = ThisWorkbook
    Set WkBk = Workbooks.Open(Filename:="file location")
    
    'Copy/Paste values one at a time....
    SrcWkBk.Sheets("Hourly Counts").Range("P4").Copy WkBk.Worksheets("Sheet1").Range("C2")
    SrcWkBk.Sheets("Hourly Counts").Range("P6").Copy WkBk.Worksheets("Sheet1").Range("C5")
    '.....etc
    
    'In this example the date is the current Date and the Shift would be in Cell A1 of the
    'wkbook the code resides in....
    WkBk.SaveAs "file location" & "_" & Format(Date, "MM-DD-YYYY") & "_" & SrcWkBk.Range("A2") & ".xlsx"
    
    'In this example the date is you want in the file name is in cell A1 of the SrcWkBk
    'wkbook the code resides in....
    WkBk.SaveAs "file location" & "_" & Format(SrcWkBk.Range("A1"), "MM-DD-YYYY") & "_" & SrcWkBk.Range("A2") & ".xlsx"
    
    'Clear Clipboard
    Application.CutCopyMode = False

End Sub

[#5]

Mike,

Thank you for the code. I imported it in to my workbook and ran it and in the cells its copying to I get "#REF!" error. Any idea why? 

Thanks, 

Doug

[#6]

   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
    '.....etc

 It might just be copying the formula.  Try pasting as values instead.

[#7]

Thank you for your quick response. Your code has allowed me to achieve a step of what I envision can we incorporate the second half? 

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. 

2nd Part of Wkbk

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. 

[#8]

Hey Douglas-

It sounds like you've been able to streamline your process very effectively.  That's of course, the great benefit to using VBA code as a solution.  Please feel free reach out again if you need additional help automating your process.