Excel Report will not copy results

Mike Orther
Mike Orther used Ask the Experts™
on
I have a button on my "Inventory" worksheet that copies items that have "On-Hand" quantity for the number I select.  It pastes the results into the OHReport worksheet.  Everything works just as I want.  

The problem I am having is when I create the same button on the "OHReport" worksheet and run it, I am prompted to enter a quantity, but no results are returned.

Private Sub SelectQtyOH_Click()

Dim OH As String
Dim x As Long
x = 2

OH = InputBox("Enter Search Quantity")
Sheets("Inventory").Select
Do While Cells(x, 1) <> ""
If Cells(x, 7) = OH Then
Worksheets("Inventory").Rows(x).Copy
Worksheets("OHReport").Activate
erow = Sheets("OHReport").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("OHReport").Rows(erow)
End If
Worksheets("Inventory").Activate
x = x + 1
Loop
Application.CutCopyMode = False

Sheets("OHReport").Select

End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
There is a better way of doing this. Use Autofilter to filter the data in column G on Inventory Sheet with the criteria entered in InputBox and then copy the filtered data to the report sheet.
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

Open in new window


If that still doesn't work for you, please upload a sample workbook and explain what exactly is not working in there.
Mike OrtherSystems Engineer

Author

Commented:
Yes, this works much better that my code.  The report is much faster.
Mike OrtherSystems Engineer

Author

Commented:
Thank you so much for this Subodh.  This works beautifully and it is so much faster than what I had
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Mike! Glad it worked as desired.
Thanks for the feedback.
Merry Christmas!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial