Forum Discussion

Douglas Schafer's avatar
Douglas Schafer
Copper Contributor
Jun 26, 2018

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

excel
Formulas and Functions
Macros and VBA
  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    Jun 27, 2018

    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 Schafer's avatar
      Douglas Schafer
      Copper Contributor
      Jun 28, 2018

      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 Mickle's avatar
        Matt Mickle
        Bronze Contributor
        Jun 28, 2018

        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