Avatar of Mike Orther
Mike OrtherFlag for United States of America

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

Microsoft OfficeVBAMicrosoft Excel

Avatar of undefined
Last Comment
Subodh Tiwari (Neeraj)
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

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)
Avatar of Mike Orther
Mike Orther
Flag of United States of America image

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
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Mike Orther
Mike Orther
Flag of United States of America image

ASKER

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

Have a great Christmas and New Year :)

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

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo