asked on

Need to limit columns copied from one sheet to another

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

End Sub

Open in new window

Subodh Tiwari (Neeraj)

Try this...

Private Sub SelectQtyOH_Click()

Dim wsData As Worksheet, wsReport As Worksheet
Dim lr 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

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("B2:E" & lr).SpecialCells(xlCellTypeVisible).Copy wsReport.Range("A" & eRow)
    End If
    .AutoFilter
End With
wsReport.Activate
Application.ScreenUpdating = True

End Sub

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)

Mike Orther

ASKER

Oh Subodh this works but I am so stupid. I actually need "B, C, D, F" I accidentally put "E"

Do you think you could help with this?

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike Orther

ASKER

Oh Subodh, once again you nailed it. Thank you so much, this worked perfectly.

Have a great Christmas and New Year :)

Mike

Subodh Tiwari (Neeraj)

You're welcome Mike!
Thanks for the feedback.
And Merry Christmas and Happy New Year to you and your family!