Copy/Paste Value Only to New Worksheet

[LBinGA]

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.

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

 

[LBinGA]

Anybody?

Thanks,
LB

 

[LBinGA]

I know this looks like it should work but it doesn't. I've been struggling with it all day. It continues to produce an "#N/A" result.
Any thoughts or help would be appreciated.

Thanks,
LB

 

[StephenCrump]

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

Here's the relevant bit of your code ....

Set rngCopyFinal = ws.Range("B36")
' ....
rngCopyFinal.Copy
rngPaste.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
rngCopyFinal.Value = ""

If rngPaste ends up with the value #N/A, then ws.Range("B36") had an #N/A value before the copy. I suggest you put in a breakpoint and check out why.

AND it strips the copied from sheet of the formula.

.... because of the last line in this code snippet!

 

[LBinGA]

rngCopyFinal = ws.Range("B36") is a Linked field, which links back to a Calculated field (=MAX(J26:J29)) on a 3rd Worksheet called Calculations. The formula is functioning properly and a figure appears in both it and in B36 on the Input sheet. If I cut/pastespecial values manually, the value pastes, no problem.

I've tried moving it directly from the Calculations sheet and the calculated field (J31) but it produces the same result, #N/A, even though there is a figure in the field. That looks like this:

'****copy FINAL
Set rngCopyFinal = ws3.Range("J31")
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 xlPasteValues
rngCopyIns.Cells(1).Select
Application.CutCopyMode = False

I get the last line thing...now...duh. Thanks.

Any thoughts on the why B36, which is linked back to a calculated field with a valid number is giving the #N/A response when I'm using pastespecial?

Thanks,
LB

 

[StephenCrump]

Any thoughts on the why B36, which is linked back to a calculated field with a valid number is giving the #N/A response when I'm using pastespecial?

Are you sure that when this piece of code runs, B36 doesn't have an #N/A value. Put in a Stop or a breakpoint and have a look.

My guess is that B36 starts out OK, but your intervening code messes something up. This looks suspicious, for example:

rngCopyIns.Value = ""

Did you mean to clear this range? Are any of your formulae dependent on this range?

 

[LBinGA]

Are you sure that when this piece of code runs, B36 doesn't have an #N/A value. Put in a Stop or a breakpoint and have a look.

My guess is that B36 starts out OK, but your intervening code messes something up. This looks suspicious, for example:

rngCopyIns.Value = ""

Did you mean to clear this range? Are any of your formulae dependent on this range?

You're absolutely right. The calculations depends on those fields being completed and that line was removing the selection and changing it to #N/A.

Thank you...SO much.

LB

 

[StephenCrump]

The calculations depends on those fields being completed and that line was removing the selection and changing it to #N/A.

Ah-hah!

Glad I could help.