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 8...
Excel
Formulas and Functions
Macros and VBA
Matt Mickle's avatar
Matt Mickle
Bronze 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 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
    • Douglas Schafer's avatar
      Douglas Schafer
      Copper Contributor
      Jun 28, 2018

      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

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

Resources