Forum Discussion
VBA transferring data
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.
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
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
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 '.....etcIt might just be copying the formula. Try pasting as values instead.
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.