VBA - .PasteSpecial question

[Ryan Saari]

Does anyone see a way to paste the range I have identified as values only and not formula's. I can't seem to figure it out using the .PasteSpecial code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("A1")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then

Sheets("Sheet1").Range(Range("A1")).CurrentRegion.Copy Sheets("Sheet2").Range("F1")
'Uses Variable in A1 to determine range to copy to Sheet 2 Cells F1:size of range
End With
End If
End Sub

Your help is appreciated!


Ryan

 

[Oorang]

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Set KeyCells = Range("A1")
    If Not Intersect(KeyCells, Target) Is Nothing Then
        Application.ScreenUpdating = False
        Me.Range("A1").Copy
        Sheet2.Range("F1").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
    End If
End Sub