PDA

View Full Version : how can i paste only value with this code?



rrosa1
03-13-2011, 12:02 PM
hi
i have following code in sh it work perfectly but in row there r some formula in some cell and i need to copy only the value from the row to anther sh
if any one can help it's greatly appreciated.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Sheets("Historical Data").Unprotect Password:=""
Sheets("All").Unprotect Password:=""
If MsgBox("Send Data to All Data Sheet?", vbYesNo) = vbYes Then
Cancel = True
Range(Cells(Target.Row, "A"), Cells(Target.Row, "AC")).Copy _
Sheets("All").Range("B" & Rows.Count).End(xlUp).Offset(1, 0) ' = Range(Cells(Target.Row, "A"), Cells(Target.Row, "AC")).Value
'Target.EntireRow.Delete 'use this for only clear the row' ClearContents
End If
Sheets("Historical Data").Protect Password:=""
Sheets("All").Protect Password:=""
End Sub


thanks for look.

JimmyTheHand
03-13-2011, 12:08 PM
Untested, but should work.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Sheets("Historical Data").Unprotect Password:=""
Sheets("All").Unprotect Password:=""
If MsgBox("Send Data to All Data Sheet?", vbYesNo) = vbYes Then
Cancel = True
Range(Cells(Target.Row, "A"), Cells(Target.Row, "AC")).Copy
Sheets("All").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlValues
' = Range(Cells(Target.Row, "A"), Cells(Target.Row, "AC")).Value
'Target.EntireRow.Delete 'use this for only clear the row' ClearContents
End If
Sheets("Historical Data").Protect Password:=""
Sheets("All").Protect Password:=""
End Sub
Jimmy

rrosa1
03-13-2011, 12:39 PM
thanks jimmy
it work great.