troubleshooting Question

Need to limit columns copied from one sheet to another

Avatar of Mike Orther
Mike OrtherFlag for United States of America asked on
Microsoft OfficeVBAMicrosoft Excel
5 Comments1 Solution128 ViewsLast Modified:
Subodh Tiwari helped me write this awesome statement.  It pulls all columns from the "Inventory" worksheet and copies to the OHReport sheet based on entered criteria.  I need to limit the number of columns copied to "B, C, D, E"

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
End With
Application.ScreenUpdating = True

End Sub

Open in new window

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros