Hello all:
I'm trying to copy & paste into another sheet the value ONLY from Input!B36 but the xlPasteValues is not working. It continues to produces #N/A as a result AND it strips the copied from sheet of the formula. I'd like to copy the Value only from the Input Sheet over to the Bordereau Sheet.
What am I missing?
Thanks,
LBinGA
I'm trying to copy & paste into another sheet the value ONLY from Input!B36 but the xlPasteValues is not working. It continues to produces #N/A as a result AND it strips the copied from sheet of the formula. I'd like to copy the Value only from the Input Sheet over to the Bordereau Sheet.
Code:
Sub CopytoBord()
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim rngCopyIns
Dim rngCopyFinal As Range
Dim rngPaste As Range
Dim LR As Long
Set ws = Worksheets("Input")
Set ws2 = Worksheets("Bordereau")
ActiveSheet.Unprotect
If ws.Range("J11").Value = "BOUND" Then
'***Copy Insured Name
Set rngCopyIns = ws.Range("C5:K5")
If ws2.Range("A2").Value = "" Then
Set rngPaste = ws2.Range("A2")
Else
LR = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1
Set rngPaste = ws2.Range("A" & LR)
End If
rngCopyIns.Copy
rngPaste.PasteSpecial xlPasteValues
rngCopyIns.Value = ""
rngCopyIns.Cells(1).Select
Application.CutCopyMode = False
'****copy Final Cost
Set rngCopyFinal = ws.Range("B36")
If ws2.Range("K2").Value = "" Then
Set rngPaste = ws2.Range("K2")
Else
LR = ws2.Cells(Rows.Count, "K").End(xlUp).Row + 1
Set rngPaste = ws2.Range("K" & LR)
End If
rngCopyFinal.Copy
rngPaste.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
rngCopyFinal.Value = ""
rngCopyFinal.Cells(1).Select
Application.CutCopyMode = False
Else
MsgBox "Must have Bound Status to move to Bordereau"
End If
ActiveSheet.Protect
End Sub
What am I missing?
Thanks,
LBinGA