Filtering by multiple columns

[kg2586]

Hopefully someone can help me figure this out.

We need to keep the regular file (AP Check Register) from our accounting system to complete account reconciliations. The problem with this is that when there are checks that are cut that have up to 5 or 6 accounts, once you filter on one of the columns for that specific account, the remaining columns that potentially have that account in it as well, does not show up. I've attached a sample file with sample detail. Is there any way to achieve what I want it to do? Adding a macro to add a new sheet and filtering by X criteria (account number) that searches all the account columns (Columns F,I,L,O,R,U,X,AA,AD,AG on sample file) and pulls in the account amount (Columns H,K,N,etc. of sample file) if it matches that criteria along with the Payee and other detailed columns would possibly suffice. Any help is greatly appreciated. Thanks!

Edit: I can't seem to attach a file. How do I do that?

 

[kg2586]

Here's a possible sample... I want the details of each of those accounts (amount, payee, check number, etc.) that are appearing in different columns to show up when specified, whether that be a macro created, formula, filter or whatever.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Account 1[/TD]
[TD]Account1 Amount[/TD]
[TD]Account2[/TD]
[TD]Account 2 Amount[/TD]
[TD]Account 3[/TD]
[TD]Account 3 amount[/TD]
[TD]Check number[/TD]
[TD]Payee[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1005000[/TD]
[TD]$5,000[/TD]
[TD]1005001[/TD]
[TD]$2,500[/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1005001[/TD]
[TD]$4,500[/TD]
[TD]1005000[/TD]
[TD]$3,000[/TD]
[TD][/TD]
[TD][/TD]
[TD]101[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1005002[/TD]
[TD]$2,500[/TD]
[TD]1005000[/TD]
[TD]$2,000[/TD]
[TD]1005001[/TD]
[TD]$1,000[/TD]
[TD]102[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[My Aswer Is This]

See if this is what you want:
Will copy the data to a sheet which already exist named "Master"

Sub Filter_To_Sheet()
'Modified 5/4/2018 11:50 AM  EDT
Application.ScreenUpdating = False
x = InputBox("Enter account number")
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "F").End(xlUp).Row
Dim counter As Long
    
    With ActiveSheet.Range("F2:FG" & Lastrow)
            .AutoFilter 1, x
            .AutoFilter 4, x
            .AutoFilter 7, x
            .AutoFilter 10, x
            .AutoFilter 13, x
            .AutoFilter 16, x
            .AutoFilter 19, x
            .AutoFilter 22, x
            
           counter = .Columns("F").SpecialCells(xlCellTypeVisible).Count
           If counter > 1 Then
            .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Master").Cells(2, 1)
           Else
           MsgBox "No valuws found"
           End If
            .AutoFilter
        End With
Application.ScreenUpdating = True
End Sub

 

[kg2586]

I got it copied in and it's failed at .AUTOFILTER 1, X saying the "autofilter method of range class failed".

 

[My Aswer Is This]

What does this mean:

I got it copied in​

You saying it did copy the values into sheet named "Master"
Then you got an error?

Or it just errored out?

Do you have a sheet named "Master"

 

[kg2586]

Sorry. I got the macro inserted. I now got it to run and tells me that no values are found. I have a sheet named "Master" and after the macro runs, the columns are filtered with no data shown, so I know the macro has the correct range for filters since the 6 columns with the accounts have the filter icon.

 

[My Aswer Is This]

So let me be sure I understand what you want.

You want to look in columns

F,I,L,O,R,U,X,AA,AD,AG ​

And in each of these columns you want to look for the account number you entered into the Inputbox

And then copy that rows data to a sheet named "Master"

Is this correct?

 

[kg2586]

Yes, that is correct.

 

[My Aswer Is This]

Now if in any of these columns it does not find the account number the script will tell you it found nothing.

 

[My Aswer Is This]

The script looks down the sheet till it finds no more data in column F
Could that be the problem?