Code to copy values over only and not formulas

[propeller]

I'm using the below to move a row from one tab to another, the problem is when it moved it over the formulas change value because they depend on different cells. Can I move values over only? Like a copy paste of the data, no calculation?



Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Lastrow = Sheets("hires").Cells(Rows.Count, "A").End(xlUp).Row + 1
If Target.Value = "Active" Then
With Rows(Target.Row)
.Copy Destination:=Sheets("hires").Rows(Lastrow)
With Application
.CutCopyMode = False
.EnableEvents = False
End With
.EntireRow.Delete
Application.EnableEvents = True
End With
Application.EnableEvents = False




Application.EnableEvents = True
End If
Application.EnableEvents = True
End If
End Sub

 

[Fluff]

Re: Can Someone please edit this code to copy values over only and not formulas

Try

With Rows(Target.Row)
Sheets("hires").Rows(Lastrow).Value = .Value

 

[propeller]

Re: Can Someone please edit this code to copy values over only and not formulas

Try

With Rows(Target.Row)
Sheets("hires").Rows(Lastrow).Value = .Value

It didn't work. I got error "Type mismatch"

 

[Fluff]

Re: Can Someone please edit this code to copy values over only and not formulas

Do any of the formulae in that row return an error?

 

[propeller]

Re: Can Someone please edit this code to copy values over only and not formulas

Do any of the formulae in that row return an error?

Once I move them to the next tab they do return an error.

 

[Fluff]

Re: Can Someone please edit this code to copy values over only and not formulas

But do any return an error on the original sheet?

 

[propeller]

Re: Can Someone please edit this code to copy values over only and not formulas

Do any of the formulae in that row return an error?

I got it to work. Thank you!!

 

[Fluff]

Re: Can Someone please edit this code to copy values over only and not formulas

Glad you sorted it & thanks for the feedback