CODEWITHSUNDEEP
vbaexcel
Kish
Kish

Reputation: 89

Dynamically Define Criteria for Selecting Data

I need some help please; I am trying to select data from one sheet and transfer to another sheet. The criteria that I am using to choose this data is "Ship" as it is stated in the line .Range("A1:H" & lRow).AutoFilter Field:=2, Criteria1:="Ship"but I want to dynamically select that criteria by using something like Criteria1:= Range("A2") so that I can define the criteria on the Excel sheet instead of having to go into the VBA code. How can I go about doing this? Thanks, Kish 

Sub ImportShipper()    
    Dim wsEff As Worksheet
    Dim wsShip As Worksheet
    Dim wsFirst As Worksheet
    Set wsEff = Worksheets("Efficiency")
    Set wsFirst = Worksheets("1")    
    Set wsShip = ActiveSheet
    wsShip.Name = wsFirst.Range("B34").Value     
    With wsEff
        Dim lRow As Long
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A1:H" & lRow).AutoFilter Field:=2, Criteria1:="Ship"
        Dim rngCopy As Range
        'All Columns A:H
        Set rngCopy = .Columns("A:H")
        'filtered rows, not including header row - assumes row 1 is headers
        Set rngCopy = Intersect(rngCopy, .Range("A1:H" & lRow), .Range("A1:H" & lRow).Offset(1)).SpecialCells(xlCellTypeVisible)
        rngCopy.Copy
        End With
    wsShip.Range("A4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Worksheets("Efficiency").ShowAllData
    End Sub

Upvotes: 0

Views: 37

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Replace:

   Criteria1:="Ship"

with:

   Criteria1:=wsWhatever.Range("A2").Value

where you use whatever worksheet you like for the appropriate A2 cell.

Upvotes: 1

Related Questions