Private Sub SelectQtyOH_Click()
Dim wsData As Worksheet, wsReport As Worksheet
Dim lr As Long, lc As Long, eRow As Long
Dim OH As String
Application.ScreenUpdating = False
Set wsData = ThisWorkbook.Worksheets("Inventory")
Set wsReport = ThisWorkbook.Worksheets("OHReport")
lr = wsData.UsedRange.Rows.Count
lc = wsData.UsedRange.Columns.Count
OH = InputBox("Enter Search Quantity")
wsData.AutoFilterMode = False
With wsData.Rows(1)
.AutoFilter field:=7, Criteria1:=OH
If wsData.Range("G1:G" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
eRow = wsReport.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
wsData.Range(wsData.Cells(2, 1), wsData.Cells(lr, lc)).SpecialCells(xlCellTypeVisible).Copy wsReport.Range("A" & eRow)
End If
.AutoFilter
End With
wsReport.Activate
Application.ScreenUpdating = True
End Sub
ASKER
ASKER
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
TRUSTED BY
Open in new window
You can also change the paste column destination in line#22 by changing the bold column letter in wsReport.Range("A" & eRow)