VBA Paste Special as Value

[coolkat99]

Hi
I dont have experience of VBA but have a code which moves a row of data from a worksheet called Report to a worksheet called Archived based on column Q = Yes. I want the code to paste the value into worksheet Archived as a value so that it removes any formulas from the data. How can I amend the code to make this happen. My code is shown below. Many thanks Will
Module 1
Sub archive()
Dim i, lastrow
Dim mytext As String
lastrow = Sheets("Report").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lastrow
mytext = Sheets("Report").Cells(i, "Q").Text
If InStr(mytext, "Yes") Then
Sheets("Report").Cells(i, "A").EntireRow.Copy Destination:=Sheets("Archived").Range("A" & Rows.Count).End(xlUp).Offset(1)
Sheets("Report").Cells(i, "A").EntireRow.Delete
End If
Next i

End Sub



Sheet 1 (Report)
Private Sub Worksheet_Change(ByVal Target As Range)
'Application.EnableEvents = False
'If Target.Column = 17 And UCase(Target) = "YES" Then
'Cells(Target.Row, Target.Column).EntireRow.Copy Destination:=Sheets("Archived").Range("A" & Rows.Count).End(xlUp).Offset(1)
'End If
'Application.EnableEvents = True





End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

 

[Fluff]

What's the last column with data?

 

[coolkat99]

What's the last column with data?

Hi - the last column in worksheet Report is column Q and it contains either Yes or No as a value

 

[Fluff]

Ok, try this

Sub archive()
   Dim i As Long, lastrow As Long
   
   With Sheets("Report")
      lastrow = .Range("A" & Rows.Count).End(xlUp).Row
      
      For i = lastrow To 2 Step -1
         If LCase(.Range("Q" & i).Value) = "yes" Then
            Sheets("Archived").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 17).Value = .Range("A" & i).Resize(, 17).Value
            .Cells(i, "A").EntireRow.Delete
         End If
      Next i
   End With
End Sub

 

[coolkat99]

many thanks Fluff that worked a treat

 

[Fluff]

Glad to help & thanks for the feedback