Forum Discussion
Douglas Schafer
Jun 26, 2018Copper Contributor
VBA transferring data
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
- Matt MickleBronze Contributor
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
- Douglas SchaferCopper Contributor
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.
- Matt MickleBronze Contributor
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