Hello! First of all let me explain that I do not know VBA code - I have pieced together my code from other forum posts and youtube. So when helping, please give me something I can easily type without changing a ton IF POSSIBLE.
So this code is to help me take some information from 1 worksheet and then send it to another worksheet (in the same workbook) to create a data dump for metric tracking. Here is the code so far:
---------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------Private Sub CommandButton1_Click() 'Create and set variables for the Call Tracking & Call Log worksheets Dim CTrk As Worksheet, CLog As Worksheet Set CTrk = Sheet1 Set CLog = Sheet4 'Create and set variables for each cell in the call tracking sheet Dim OrdDate As Range, PrtNo As Range, WrkOrd As Range Dim PoNo As Range, CSComp As Range, CompDate As Range Set OrdDate = CTrk.Range("E13") Set PrtNo = CTrk.Range("A13") Set WrkOrd = CTrk.Range("C13") Set PoNo = CTrk.Range("D13") Set CSReq = CTrk.Range("I13") Set CSComp = CTrk.Range("T8") Set CompDate = CTrk.Range("R13") 'Create a variable for the paste cell in the Call Log worksheet Dim DestCell As Range If CLog.Range("A2") = "" Then 'If A2 is empty Set DestCell = CLog.Range("A2") '...then destination cell is A2 Else Set DestCell = CLog.Range("A1").End(xlDown).Offset(1, 0) '...otherwise the next empty row End If 'If no "Number called has been entered, exit macro If PrtNo = "" Then MsgBox "You must enter a Part Number before adding to the log" Exit Sub End If 'Copy and paste data from the Call Tracking worksheet to the Call Log worksheet OrdDate.Copy DestCell PrtNo.Copy DestCell.Offset(0, 1) WrkOrd.Copy DestCell.Offset(0, 3) PoNo.Copy DestCell.Offset(0, 4) CSReq.Copy DestCell.Offset(0, 5) CSComp.Copy DestCell.Offset(0, 6) I NEED THIS TO BE A PASTE VALUE ONLY - NOT FORMULA CompDate.Copy DestCell.Offset(0, 7) End Sub
The section of the code where I turned the text to red is where I need help. Currently this code works perfectly for what I need except this one line always pastes the formula, and understandably so, but I need it to be value only. In fact, all of them can be value only...if that helps you help me. But this is the one that I need,
Thank you so much!
Bookmarks