0

not sure where to start this one as im still developing my VBA abilities.

I have the code below which Searches in column Q for the word "Yes", and then moves that row to a new sheet named "Archive", then deletes that row from the "Quality Log"

The issue at hand is that it pastes the whole row including formulas but i just want the code to paste Values only.

any ideas on i can change this code to do so. cheers in advance.

Sub ArchiveReworked()
    Dim xRg As Range
    Dim xCell As Range
    Dim i As Long
    Dim j As Long
    Dim K As Long
    i = Worksheets("Quality Log").UsedRange.Rows.Count
    j = Worksheets("Actioned").UsedRange.Rows.Count
    If j = 1 Then
       If Application.WorksheetFunction.CountA(Worksheets("Actioned").UsedRange) = 0 Then j = 0
    End If
    Set xRg = Worksheets("Quality Log").Range("Q4:Q" & i)
    On Error Resume Next
    Application.ScreenUpdating = False
    For K = 1 To xRg.Count
        If CStr(xRg(K).Value) = "Yes" Then
            xRg(K).EntireRow.Copy Destination:=Worksheets("Actioned").Range("A" & j + 1)
            xRg(K).EntireRow.Delete
            If CStr(xRg(K).Value) = "Yes" Then
                K = K - 1
            End If
            j = j + 1
        End If
    Next
   

Call ResizeArchiveTable
      
    Application.ScreenUpdating = True
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Tomas Joe Gemine
  • 81
  • 10
  • 3
    Remove `On Error Resume Next` this line is toxic as it hides all errors until `End Sub`. The issue is it hides the error messages but the errors still occur. If you cannot see your errors you cannot fix them, if you don't fix them your code cannot work properly. It is just like saying *"hey VBA if there are any errors just let them happen and don't tell me, I don't care if something is going wrong"*. • [VBA Error Handling – A Complete Guide](https://excelmacromastery.com/vba-error-handling) – Pᴇʜ Jul 14 '22 at 14:58
  • Format your data on the "Quality Log" sheet as a Table. Now you have no need to do any resizing and you have a filterable data source. Filter on the data in Column Q for "Yes" and you have a set of data that you can easily copy and paste as values. Then simply delete the filtered rows and remove the filter. You've gotten rid of the loop, organized your data in a meaningful manner and are using the right tools for the job athand. – Frank Ball Jul 14 '22 at 16:33

0 Answers0