Copy specific rows based on text in a specific column to a different sheet

Question

I am currently tryinig to hardcopy data from one sheet1 (Output for Exact) to a different sheet2 (Hard Output Accruals). Column I in both sheets provides a description that can be either Mark-up or Accrual. My goal is to hard copy all rows from sheet1 to sheet2 where Column I indicates an Accrual. However, when I try to run my code, it does not work (select method of range class failed).

Any suggestions?

PS. the code is based on the following tutorial: https://www.youtube.com/watch?v=qGZQIl9JJk4

Kind regards,

Hajo

Here is the code:

Sub Coentunnel_Accruals()
' Coentunnel Macro

 If Worksheets("Checks").Range("C2").Value > 0.01 Then
        
        MsgBox "One or multiple checks is/are invalid"
        
        Exit Sub
    End If

    a = Worksheets("Output for Exact").Cells(Rows.Count, 9).End(xlUp).Row
    
        For i = 2 To a
    
            If Worksheets("Output for Exact").Cells(i, 9).Value = "Accruals" Then
                
                Worksheets("Output for Exact").Rows(i).Copy
                Worksheets("Hard Output Accruals").Activate
                b = Worksheets("Hard Output Accruals").Cells(Rows.Count, 1).End(xlUp).Row
                Worksheets("Hard Output Accruals").Cells(b + 1, 1).Select
                ActivateSheet.PasteSpecial Paste:=xlPasteValues
                Worksheets("Output for Exact").Activate
            
            End If
    Next
     
Application.CutCopyMode = False
ThisWorkbook.Worksheets("Output for Exact").Cells(1, 1).Select

End Sub 

I tried changing the codes where I would not activate any sheets and not select any sheets. I first used the following simple code:

If Worksheets("Output for Exact").Range("I2").Value = "Accrual" Then

Worksheets("Output for Exact").Range("A2:W39").Copy
Worksheets("Hard Ouput Accruals").Range("A2").PasteSpecial Paste:=xlPasteValues

However, this only considers the value in I2, and does not go through all rows to see whether it is an accrual and only copies the data for which I2 is the accrual.

Answer 1

This is an oversimplified version of your code... but I think you can combine the two/edit as needed from there.

Essentially in my dumbed down version I was copying all rows with the words "Yes" in column I on Sheet3, then pasting them as values on Sheet4. In your scenario obviously change "Yes" to "Accruals" and change Sheet3 and Sheet4 to the names of your Sheets

Sub CopyRows()
    Dim i As Long, j As Long, lastRow As Long
    lastRow = Sheets("Sheet3").Cells(Rows.Count, 9).End(xlUp).Row
    j = 1
    For i = 1 To lastRow
        If Sheets("Sheet3").Cells(i, 9).Value = "Yes" Then
            Sheets("Sheet3").Rows(i).Copy
            Sheets("Sheet4").Rows(j).PasteSpecial xlPasteValues
            j = j + 1
        End If
    Next i
End Sub

Answer 2

why don't you use the easy ways?

Sub CopyLines()
Const iRow as Long
Sheets(1).Activate
Dim x as Long, y as Long, count as long
x=Sheets(1).Activesheet.UsedRange.Rows.count
y=Sheets(1).Activesheet.UsedRange.Columns.count
Dim Result (x,y) as Variant' result array
count=0
For i=1 to x
  If Cells(i,iRow)=<your condition> then 
    For j=1 to y
      count=count+1
      Result(count,j)=Cells(i,j)
    Next j
  End If
  Sheets(2).Activate
  For i=1 to count
    for j=1 to y
      Cells(i,j)=Result(i,j)
    Next j
  Next i
End sub