Search, Copy Paste Macro

Originally Posted by MarvinP

Try this code

Sub CopyToSheet2()

Dim LookFor As Variant
Dim LastRow As Double
Dim RowCtr As Double
Dim WS2 As Worksheet
Dim Last2Row As Double

Set WS2 = ActiveWorkbook.Worksheets("Sheet2")
LookFor = InputBox("What are you looking for?")
LastRow = Cells(Rows.Count, "Q").End(xlUp).Row

    For RowCtr = 1 To LastRow
        If Cells(RowCtr, "Q").Text = LookFor Then
            Cells(RowCtr, "Q").EntireRow.Copy
            With WS2
                Last2Row = .Cells(Rows.Count, "Q").End(xlUp).Row
                .Cells(Last2Row + 1, "A").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
            End With
            Cells(RowCtr, "H") = Cells(RowCtr, "H") - 1 ' Subtract 1 from On-Hand Column
        End If
    Next RowCtr
    
Application.CutCopyMode = False
End Sub

That has the subtraction issue knocked out .. it works great .... is there a way i can run that macro on sheet three instead of sheet 1 and it do the exact same thing?