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