VBA is sending Formula not Value of a box

[thekeymaster]

End If

'DEST CELL IS CELL C4 SO ALL CELLS AFTER THIS ARE OFFSET BY ONE LINE SO REPD IS ONE CELL RIGHT OF DEST CELL C4'
LOC.Copy DestCell
REPD.Copy DestCell.Offset(0, 1)
APP.Copy DestCell.Offset(0, 11)
VIN.Copy DestCell.Offset(0, 2)
VEH.Copy DestCell.Offset(0, 3)
'LOCATION (0,4)(0,5) IS VEHICLE MILEAGE/RUN STATUS'
CTL.Copy DestCell.Offset(0, 6)
LHD.Copy DestCell.Offset(0, 7)
KT.Copy DestCell.Offset(0, 9)
VDR.Copy DestCell.Offset(0, 8)
KC.Copy DestCell.Offset(0, 10)
'LOCATION (0,11) IS KEY DAYS STORED
DTC.Copy DestCell.Offset(0, 12)
CST.Copy DestCell.Offset(0, 14)
BLD.Copy DestCell.Offset(0, 15)
INV.Copy DestCell.Offset(0, 16)

'CLEARS THE CONTENTS OF THE LINES ON THE KIP ONCE TRANSFER IS COMPLETE'
LOC.ClearContents

I'm a little new to vba and I've created this code. My problem is that line APP and line DTC need to send a value and right now they're sending the formula. I've tried pastespecial for value or text but I've only succeeded in generating different variations of errors. I was wondering if anyone might have some suggestion to help me. I'd really appreciate it.

 

[Fluff]

Hi & welcome to MrExcel.
If they are just single cells try

DestCell.Offset(0, 11).Value=APP.Value

 

[thekeymaster]

Hi & welcome to MrExcel.
If they are just single cells try

DestCell.Offset(0, 11).Value=APP.Value

Thanks!

hmm...I tried throws error "run time error '1004": copy method of range class failed

 

[thekeymaster]

Thanks!

hmm...I tried throws error "run time error '1004": copy method of range class failed

Private Sub Button15_Click() 'row 4

Dim KIP As Worksheet, COMPLETED As Worksheet

Set KIP = Sheet1
Set COMPLETED = Sheet17

'CREATES AND SETS THE VARIABLES THAT WILL BE USED AS REFERENCES FOR THE CODE TO IDENTIFY EACH VALUE ON THE SHEET'
Dim LOC As Range, REPD As Range, APP As Range, VIN As Range
Dim VEH As Range, CTL As Range, LHD As Range, KT As Range
Dim VDR As Range, KC As Range, DTC As Range, CST As Range
Dim BLD As Range, INV As Range

'THESE ARE NON TRANSFER RANGES INCLUDED BECAUSE THEY ARE USED IN CLEAR CONTENTS ROUTINE
Dim DKC As Range, OL As Range, KAD As Range, DLC As Range


'EACH OF THESE SHOULD CORRELATE WITH THE SAME ORDER THEY APPEAR ON THE SPREADSHEET'
Set LOC = KIP.Range("D4") 'LOCATION
Set REPD = KIP.Range("E4") 'REPO DATE
'F,G,H CALCULATION COLUMNS
Set APP = KIP.Range("I4") 'DAYS TILL KEY APPROVED
Set VIN = KIP.Range("J4") 'VIN
Set VEH = KIP.Range("K4") 'VEHICLE DESCRIPTION
Set CTL = KIP.Range("L4") 'CLIENT
Set LHD = KIP.Range("M4") 'LEINHOLDER
Set KT = KIP.Range("N4") 'KEY TYPE
'O CALUCULATION COLUMN
Set VDR = KIP.Range("P4") 'VENDOR
Set KC = KIP.Range("Q4") 'KEYCODE
'R CALCULATION COLUMN
Set DTC = KIP.Range("S4") 'DAYS TO COMPLETE KEY
Set CST = KIP.Range("T4") 'COST TO CREATE KEY
Set BLD = KIP.Range("U4") 'COST BILLED TO CLIENT
Set INV = KIP.Range("V4") 'INVOICE NUMBER

 'END OF TRANSFERRED DATA FOLLOWING ARE NOT IN ORDER
Set DKC = KIP.Range("R4") 'DATE KEY WAS COMPLETE - NEEDS TO BE CLEARED
Set OL = KIP.Range("H4") 'DAYS ON LOT - NEEDS TO BE CLEARED
Set KAD = KIP.Range("G4") 'DATE KEY APPROVED - NEEDS TO BE CLEARED
Set DLC = KIP.Range("O4") 'DATE KEY REQUESTED FROM VENDOR - NEEDS TO BE CLEARED

'SEQUENCE BELLOW DIRECTS TRANSFER TO FIRST DEST SELL LOCATION C4 ON COMPLETED
Dim DestCell As Range
If COMPLETED.Range("C4") = "" Then
    Set DestCell = COMPLETED.Range("C4")
  
Else
    Set DestCell = COMPLETED.Range("C4").End(xlDown).Offset(1, 0)
    
 'BELOW IS CODE FOR MESSAGE BOX
End If
If VIN = "" Or INV = "" Then
    MsgBox "EITHER VIN OR INVOICE INFORMATION IS MISSING. PLEASE UPDATE AND RETY (NO DATA HAS BEEN TRANSFERRED)"
    Exit Sub
   
End If

'DEST CELL IS CELL C4 SO ALL CELLS AFTER THIS ARE OFFSET BY ONE LINE SO REPD IS ONE CELL RIGHT OF DEST CELL C4'
LOC.Copy DestCell
REPD.Copy DestCell.Offset(0, 1)
APP.Copy DestCell.Offset(0, 11).Value = APP.Value
VIN.Copy DestCell.Offset(0, 2)
VEH.Copy DestCell.Offset(0, 3)
'LOCATION (0,4)(0,5) IS VEHICLE MILEAGE/RUN STATUS'
CTL.Copy DestCell.Offset(0, 6)
LHD.Copy DestCell.Offset(0, 7)
KT.Copy DestCell.Offset(0, 9)
VDR.Copy DestCell.Offset(0, 8)
KC.Copy DestCell.Offset(0, 10)
'LOCATION (0,11) IS KEY DAYS STORED
DTC.Copy DestCell.Offset(0, 12)
CST.Copy DestCell.Offset(0, 14)
BLD.Copy DestCell.Offset(0, 15)
INV.Copy DestCell.Offset(0, 16)

'CLEARS THE CONTENTS OF THE LINES ON THE KIP ONCE TRANSFER IS COMPLETE'
LOC.ClearContents
REPD.ClearContents
APP.ClearContents
VIN.ClearContents
VEH.ClearContents
CTL.ClearContents
LHD.ClearContents
KT.ClearContents
VDR.ClearContents
KC.ClearContents
DTC.ClearContents
CST.ClearContents
BLD.ClearContents
INV.ClearContents
'NON TRANSFER CELLS
DKC.ClearContents
OL.ClearContents
KAD.ClearContents
DLC.ClearContents



End Sub

whole code appears as such

 

[Fluff]

Replace your original line with the code I posted.

 

[thekeymaster]

Replace your original line with the code I posted.

ahhh! that worked thank you so much! I miss understood and just added it as an addition. Thank you. I really appreciate it.

 

[Fluff]

You're welcome & thanks for the feedback.