Excel - how to copy all colums in a row based on a condition

Question

I'm new to macros and hope somebody can help Trying to make a report from a datasheet that meets some conditions. I need to fetch all the data (cells) in a row e.g. that meet the condition <1 in column 10 and copy those cells/row to a new sheet. I cant find any functions i Excel so I hope a not to advanced macro which I can modify can do the trick. Thanks in advance.

Answer 1

• Open a copy of your excel sheet containing all the data
• press ALT+F11 to open the VBA editor

• open sheet1 from the left pane and insert the code below

  Sub mymacro()
    For myrow = 1 To Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
      If Cells(myrow, 8) < 1 Then
        i = i + 1
        ActiveWorkbook.Sheets(1).Rows(myrow).Copy Destination:=ActiveWorkbook.Sheets(2).Rows(i)
      End If
    Next myrow
  End Sub
  

• go back to your sheet and press ALT+F8 and execute your macro

This code will run until the last used row, check if the value of 8th column is < 1 and if yes, copy the whole row to the next free row in sheet 2.