Auto Filter From Sh1 / Sh2

[Edgarvelez]

Hi all,

Cant quite figure this one out how to write the last line, I ma not writing it correctly.
I am filtering in sh2 based on sh1.

    Dim sh1 As Worksheet, sh2 As Worksheet
    Set sh1 = Sheets("DashBoard")
    Set sh2 = Sheets("Sheet1")
    Select Case sh1.Range("B3").Value
    Case "Sheet Line No."
    
   Dim Ary As Variant
With sh1.Range("A7", .Range("A" & Rows.Count).End(xlUp))
         Ary = .Worksheet.Evaluate("transpose(if({1}," & .Address & "&""""))")
      End With
   End With
      Sh2.("Sh1.Range("A2:G2").AutoFilter 1, Ary, xlFilterValues
End Sub

 

[Fluff]

Are you trying to filter sh2, based on the values in sh1 A7 downwards?

 

[Edgarvelez]

Hi,
Actually it was your code originally you provided me below but I have changed it because I am using case.

Sub Edgarvelez()
   Dim Ary As Variant
   
   With Sheets("DashBoard")
      With .Range("I4", .Range("I" & Rows.Count).End(xlUp))
         Ary = .Worksheet.Evaluate("transpose(if({1}," & .Address & "&""""))")
      End With
   End With
   Sheets("Sheet1").Range("A2:G2").AutoFilter 1, Ary, xlFilterValues
End Sub

 

[Edgarvelez]

Yes values A7 downwards

 

[Fluff]

Can you please explain exactly what you want to do?

 

[Edgarvelez]

Trying to filter sh2, based on the values in sh1 A7 downwards using the format below sh1 = Sheets("DashBoard") and sh2 = Sheets("Sheet1")

    Dim sh1 As Worksheet, sh2 As Worksheet
    Set sh1 = Sheets("DashBoard")
    Set sh2 = Sheets("Sheet1")
    
    
    Select Case sh1.Range("B3").Value
    Case "Sheet Line No."
    
   Dim Ary As Variant
With sh1.Range("A7", .Range("A" & Rows.Count).End(xlUp))
         Ary = .Worksheet.Evaluate("transpose(if({1}," & .Address & "&""""))")
      End With
   End With
      Sh2.(Sh1.Range("A2:G2").AutoFilter 1, Ary, xlFilterValues
End Sub

 

[Fluff]

But what about the select case, what is that for?

 

[Edgarvelez]

Because I will have 2 variants of the code depending on what I select in B3 of sh1.
The first variant of the code will be:
("A2:G2").AutoFilter 1, Ary, xlFilterValues
The second Variant of the code will be
("C2:G2").AutoFilter 1, Ary, xlFilterValues
once you help me with the first I can do the second and not bother too much.

 

[Fluff]

Ok, how about

   Dim Ary As Variant
   Set sh1 = Sheets("DashBoard")
   Set sh2 = Sheets("Sheet1")
   
   Select Case sh1.Range("B3").Value
      Case "Sheet Line No."
      
      With sh1.Range("A7", sh1.Range("A" & rows.count).End(xlUp))
         Ary = .Worksheet.Evaluate("transpose(if({1}," & .Address & "&""""))")
      End With
      sh2.Range("A2:G2").AutoFilter 1, Ary, xlFilterValues
   End Select

 

[Edgarvelez]

Awesome it worked thank you so much.
This it what the full code looks like:

   Set sh1 = Sheets("DashBoard")
   Set sh2 = Sheets("Sheet1")
   
   
   Dim Ary As Variant
   Select Case sh1.Range("B3").Value
      Case "Sheet Line No."
      With sh1.Range("A7", sh1.Range("A" & Rows.Count).End(xlUp))
         Ary = .Worksheet.Evaluate("transpose(if({1}," & .Address & "&""""))")
      End With
      sh2.Range("A2:G2").AutoFilter 1, Ary, xlFilterValues
      
      Case "Batch No."
      With sh1.Range("A7", sh1.Range("A" & Rows.Count).End(xlUp))
         Ary = .Worksheet.Evaluate("transpose(if({1}," & .Address & "&""""))")
      End With
      sh2.Range("C2:G2").AutoFilter 1, Ary, xlFilterValues
      
   End Select
    
End Sub