copy entire row if X value is in row, paste in 2nd worksheet to create a table.

[needhelp9009]

Hi,

problem:
i have a table of data. i would like to find the rows which has the values "overtime", copy and paste them into another sheet.
i managed to do it by recording macros of filtering the value from the column, copy using ctrl shift down right and paste entire list of data into a new sheet.
Is there a better way to do this, in the event the data pulled out the value "overtime" is not in the same column all the time? e.g. next month instead of column M, the overtime is in column N

Another way i have tried is to copy and paste a formula where $A$1 i enter the value overtime
=INDEX(Working2!$A$1:$O$195, SMALL(IF(COUNTIF($A$1,Working2!$A$1:$O$195), MATCH(ROW(Working2!$A$1:$O$195), ROW(Working2!$A$1:$O$195)), ""), ROWS(Working2!$A$1:A1)), COLUMNS(Working2!$A$1:A1))
but in this case, i have to drag the formula to 500 rows (just to be sure that it pulls out all the values from the original table)
is there a better way of doing this?

Sub createsheet()
'
' createsheet Macro


'set variablefor last used row in the data range
    Dim lr As Long

    Sheets("Working2").Select
    
    'Stop screen flickering
    Application.ScreenUpdating = False
    
    'Get the last used row in Column A - if its row 100 or 1000
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
    'Print lr 's value to the immediate Window
    Debug.Print lr
    
    
    Range("A1:O1").Select
    Selection.Copy
      
' add a sheet after the active sheet
    Sheets.Add After:=ActiveSheet

With ActiveSheet

'enter the values you want to filter
    Range("A1") = "overtime"

    
'Select and paste header row
    Range("A6").Select
    .Paste
    Application.CutCopyMode = False
    
'Insert formula into the cells
    Range("A7").Select
    ActiveCell.Formula2R1C1 = _
        "=INDEX(Working2!R1C1:R" & lr & "C15, SMALL(IF(COUNTIF(R1C1,Working2!R1C1:R" & lr & "C15), MATCH(ROW(Working2!R1C1:R" & lr & "C15), ROW(Working2!R1C1:R" & lr & "C15)), """"), ROWS(Working2!R1C1:R[-6]C[0])), COLUMNS(Working2!R1C1:R[-6]C[0]))"

    
'Fill formula for rest of col
    Selection.AutoFill Destination:=Range("A7:O7"), Type:=xlFillDefault
    
'Change format
    Range("A7").Select
    Selection.NumberFormat = "m/d/yyyy"
    Range("M7:N7").Select
    Selection.NumberFormat = "h:mm:ss AM/PM"
    Range("B7,D7:F7,O7").Select
    Selection.NumberFormat = "0"
    Range("A7:O7").Select
    Selection.AutoFill Destination:=Range("A7:O500"), Type:=xlFillDefault
    Columns("A:O").EntireColumn.AutoFit

    
    'Turn back on screen flickering
     Application.ScreenUpdating = True

End With

End Sub