VBA: Paste Special (Values)

Here I have written three procedure hop[e they help, chose which is for you, a bit more code but gets around all the issues you have and is abetter way to program for your task, IMO [in my opinion]

Rdgs

Jack

Sub BestVersion()
' Jack in the UK
' [url]www.excel-it.com[/url]


'//Range("A1:A2").Copy Destination:=Range("C3")
'
Dim rSource As Excel.Range
Dim rDestination As Excel.Range
Set rSource = ActiveSheet.Range("a1:a2")
Set rDestination = ActiveSheet.Range("c3")


rSource.Copy
rDestination.Select


Selection.PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, _
    SkipBlanks:=False, _
    Transpose:=False
    
Range("A1").Select
    
Application.CutCopyMode = False


valKill:
Set rSource = Nothing
Set rDestination = Nothing


Exit Sub


End Sub

Sub LongHandVersion()
' Jack in the UK
' [url]www.excel-it.com[/url]


'//Range("A1:A2").Copy Destination:=Range("C3")
'
Range("A1:A2").Select


Selection.Copy
Range("C3").Select


Selection.PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False


Range("A1").Select


Application.CutCopyMode = False


End Sub

Sub LongHandVersion_Better()
' Jack in the UK
' [url]www.excel-it.com[/url]


'//Range("A1:A2").Copy Destination:=Range("C3")
'
Range("A1:A2").Copy
Range("C3").Select


Selection.PasteSpecial Paste:=xlPasteValues
    
Range("A1").Select


Application.CutCopyMode = False


End Sub

Re: VBA: Paste Special (Values)

Hello Everbody
I have assigned a task in which there is one master sheet which uses the msgbox option to ask that which slave worksheet you want to copy then when we select the other sheet then it must copy the value and there is another restriction which is that it must copy the value from the slave sheet when the cell in the slave sheet is green, And also offcourse there will e the formulas in the other sheet so in the master sheet there must be the PasteSpecial command to be used.

I hope i will get the relevent answer.

Re: VBA: Paste Special (Values)

This works great except for one thing. It duplicates conditional formating every time it copies. Is there a way to make it "paste special values" and leave the rest alone?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rFrom As Range
    If Target.Count = 1 Then
        With Target.Worksheet
             Set rFrom = .Range("A2:C280")
             If Not Intersect(Target, rFrom) Is Nothing Then
                 Application.EnableEvents = False
                 'Include next line Just in Case something happens
                 '    You don't want to leave EnableEvents off
                 On Error Resume Next
                 rFrom.Copy Worksheets("RS Scale").Range("A2:C280")
                 If Err.Number <> 0 Then
                     MsgBox "Error Occurred"
                 End If
                 Application.EnableEvents = True
             End If
         End With
    End If
End Sub

Re: VBA: Paste Special (Values)

I got this to work. It's cleaner and faster. But if I highlight and clear multiple cells or press delete it does not erase it from the second sheet. I can live with that but if anyone has any tips to do make deleting multiple cells work, that would be great.

This version goes in the CS Scale sheet and a reverse sheet order goes in the RS Scale sheet. Works great.

Private Sub Worksheet_Change(ByVal Target As Range)Dim rFrom As Range
If Target.Count = 1 Then
With Target.Worksheet
Worksheets("RS Scale").Range("A2:C500").Value = Worksheets("CS Scale").Range("A2:C500").Value
End With


End If


End Sub

Quote from Matt Heldman;608021

This works great except for one thing. It duplicates conditional formating every time it copies. Is there a way to make it "paste special values" and leave the rest alone?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rFrom As Range
If Target.Count = 1 Then
With Target.Worksheet
Set rFrom = .Range("A2:C280")
If Not Intersect(Target, rFrom) Is Nothing Then
Application.EnableEvents = False
'Include next line Just in Case something happens
' You don't want to leave EnableEvents off
On Error Resume Next
rFrom.Copy Worksheets("RS Scale").Range("A2:C280")
If Err.Number <> 0 Then
MsgBox "Error Occurred"
End If
Application.EnableEvents = True
End If
End With
End If
End Sub

Display More

Re: VBA: Paste Special (Values)

Matt,

Please use code tags for all code. I have added them for you this time.