VBA Advanced Filter, return all columns if value matches in 1 column

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,696
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have the code below, which only returns values for first column, not all columns.

Sheet: wTransactions
Table: tbl_Transactions in range A1:L40981
Filter range: AA1:AL2, where AA1:AL1 = A1:L1 (headers match those of tbl_Transactions)
Filter criteria: AC2, a value that exists in C1:C40981, AA2:AB2, AD2:AL2 are empty

Can anyone suggest correction to code below so it returns values across AA4:ALx (where x is count of filtered records + 3), it only returns filtered values only in AA4 and down, not AA4:AL4 and down:
Code:
Sub Macro3()
    
    With wTransactions
        With .[Temp_Output]
            .Offset(1).Resize(, 12).Value = ""
            .Offset(1, 2).Value = UCase("Bank Charges") '[Account_User]
        End With
        .[tbl_Transactions[#All]].AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.[AA1:AL2], CopyToRange:=.[AA4]
    End With
            
End Sub
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,886
Office Version
  1. 365
Platform
  1. Windows
Do you clear the copytorange, before running the filter?
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,696
Office Version
  1. 365
Platform
  1. Windows
Hey Fluff, yes I hadn't cleared the range out, figured it out earlier today but forgot to update back here. Thanks for taking a look :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,886
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,788
Messages
5,574,291
Members
412,584
Latest member
Leow
Top