Macro to find all negative values in a column and paste the rows to a new sheet

[hinch42]

Hello,

I am looking for a macro that can find all negative values in a column and then copy the row associated with the negative column value and past into a new worksheet in the same work book. The data is for accounting purposes. The column in question is "P" which are days late. When the days are negative that means they are late so I need to find a way to select rows which have late days present. I'm hoping to eventually automate the macro through power automate but that is next weeks battle.

Thank you all! Enjoying the forum, it is a great resource.

 

[DanteAmor]

Hi and welcome to MrExcel

Try this:

Sub copyrows()
  Dim sh1 As Worksheet
  Set sh1 = Sheets("Sheet1")    'fit to the name of your sheet
  
  If sh1.AutoFilterMode Then sh1.AutoFilterMode = False
  sh1.Range("A1:P" & sh1.Range("P" & Rows.Count).End(3).Row).AutoFilter 16, "<0"
  sh1.AutoFilter.Range.Copy
  Sheets.Add , Sheets(Sheets.Count)
  ActiveSheet.Range("A1").PasteSpecial xlPasteValues
  sh1.ShowAllData
End Sub

 

[hinch42]

Hi and welcome to MrExcel

Try this:

Sub copyrows()
  Dim sh1 As Worksheet
  Set sh1 = Sheets("Sheet1")    'fit to the name of your sheet
 
  If sh1.AutoFilterMode Then sh1.AutoFilterMode = False
  sh1.Range("A1:P" & sh1.Range("P" & Rows.Count).End(3).Row).AutoFilter 16, "<0"
  sh1.AutoFilter.Range.Copy
  Sheets.Add , Sheets(Sheets.Count)
  ActiveSheet.Range("A1").PasteSpecial xlPasteValues
  sh1.ShowAllData
End Sub

[
[/QUOTE]

That worked, thank you!