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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,087
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
51,087
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,183
Messages
5,576,574
Members
412,734
Latest member
rubinrods
Top