Help with Filter Macro

[DaleRW]

Hello,

I have a workbook that is used to bring in raw data for POS transactions from multiple locations. The macro used is to filter the data on a summary page as well as on individual worksheets for each location so on each worksheet only that location's transactions show and they are filtered so all Visa, MC, AMEX or Debit are together and each has a subtotal as well as a location grand total.

The macro we use to filter the information worked well at first but now when we hit the filter button it sorts it so only each location's transactions show on their worksheet but the worksheet is not sorted to have each type of transaction together and there are no subtotals, just a grand total for each location. If we hit the filter button a second time then everything gets sorted and filtered correctly.

If anyone can help with two things. First, how do I fix this so we only have to hit the filter button once and second, is there a way to combine all these lines into one that will filter all worksheets?

The macro we are using is:

Sub FilterSheet()
'
' FilterSheet Macro
'

'
    Sheets("(01)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(03)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(04)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(07)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(08)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(09)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(10)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(12)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(13)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(14)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(15)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(16)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(17)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(19)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(21)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(23)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(28)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(29)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(30)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(31)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(33)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(34)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(35)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(36)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(38)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(39)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(40)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(51)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(52)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(53)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(54)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(55)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(56)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(57)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(58)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(59)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(60)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("(62)").Select
    ActiveSheet.Range("$A$1:$A$3392").AutoFilter Field:=1, Criteria1:="<>"
    Sheets("Summary").Select
    ActiveSheet.Range("$A$2:$A$118759").AutoFilter Field:=1, Criteria1:="<>"
    
End Sub

 

[mse330]

Hi DaleRW,

The below code should do whatever the above code posted by you was doing. However, I don't see anything in your code regarding the subtotal/grand total or sorting the data. Is there another code you are running ?

Sub FilterSheet_Updated()
Dim ws As Worksheet
For Each ws In Worksheets
    Select Case ws.Name
        Case "(01)", "(03)", "(04)", "(07)", "(08)", "(09)", "(10)", "(12)", _
             "(13)", "(14)", "(15)", "(16)", "(17)", "(19)", "(21)", "(23)", _
             "(28)", "(29)", "(30)", "(31)", "(33)", "(34)", "(35)", "(36)", _
             "(38)", "(39)", "(40)", "(51)", "(52)", "(53)", "(54)", "(55)", _
             "(56)", "(57)", "(58)", "(59)", "(60)", "(62)"
            ws.Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:="<>"
        Case "Summary"
            ws.Range("A2").CurrentRegion.AutoFilter Field:=1, Criteria1:="<>"
    End Select
Next
End Sub

 

[DaleRW]

Thanks for the response and I will give that a shot to reduce the size of the macro.

Actually, the subtotal and grand total are hard coded into each worksheet but whether they print or not is based on if the subtotal or grand total has a balance different than $0 or not. If the balance is =0 then it is not printed but if it is <>0 then it does.

 

[DaleRW]

Ok, I replaced the macro I had with the one you put above and it does work to filter the pages and it seems to do it much faster but I still have to hit the macro button twice. Is it because the subtotals and totals are hard coded with forumlas on each worksheet? A co-worker originally created this workbook for me. He understands Macros a little more than I do but not enough to do them on his own without getting this type of help. Basically the way he has set it up is that the raw data is converted to somewhat formatted data on a separate worksheet that is hidden. He then created a worksheet for each location where he just copies the formatted data over in 4 different sections (Visa, MC, Amex, Dbt) and then through formulas he has a "P" (to print that line) put in column A on that line where those payments are the type of payment that section is for (subtotal line will have VI, MC AM or PV on it) and for that branch the worksheet is for. I am not sure if that makes sense or not. So, when you go to a worksheet for a specific location it will only have visible the lines with a P in column A which should include the subtotals for each type of payment (if there are any payments of that type) and the "Branch Total" (grand total).

 

[mse330]

If I understand correctly, an IF forumla is placed in column A to either show blank or show the letter "P" if that row is needed to be filtered. If that is the case, try replacing

Criteria1:="<>"

with this

Criteria1:="P"​

 

[DaleRW]

Hi again and thanks for your reply. I appreciate it! I replaced "<>" with "P" and it still works but I am still needing to hit the filter button twice. For more clarity we have the following:
Raw Data:

11*****	RME	103****	2018-08-22 06:46:45.423	D--------	204******	P	1464	1464	0	-	1	-	VI		-	-	Sol-----		2--------	B-----		---	--	--	--	Y	-	---		--	54JN	OMS--

<colgroup><col style="width:48pt" width="64" span="3"> <col style="mso-width-source:userset;mso-width-alt:5632;width:116pt" width="154"> <col style="width:48pt" width="64" span="31"> </colgroup><tbody>
[TD="width: 64"]merchant_id[/TD]
[TD="width: 64"]merchant_name[/TD]
[TD="width: 64"]trn_id[/TD]
[TD="width: 154"]trn_datetime[/TD]
[TD="width: 64"]trn_card_owner[/TD]
[TD="width: 64"]trn_ip[/TD]
[TD="width: 64"]trn_type[/TD]
[TD="width: 64"]trn_amount[/TD]
[TD="width: 64"]trn_original_amount[/TD]
[TD="width: 64"]trn_returns[/TD]
[TD="width: 64"]trn_order_number[/TD]
[TD="width: 64"]trn_batch_number[/TD]
[TD="width: 64"]trn_auth_code[/TD]
[TD="width: 64"]trn_card_type[/TD]
[TD="width: 64"]trn_adjustment_to[/TD]
[TD="width: 64"]trn_response[/TD]
[TD="width: 64"]message_id[/TD]
[TD="width: 64"]b_name[/TD]
[TD="width: 64"]b_email[/TD]
[TD="width: 64"]b_phone[/TD]
[TD="width: 64"]b_address1[/TD]
[TD="width: 64"]b_address2[/TD]
[TD="width: 64"]b_city[/TD]
[TD="width: 64"]b_province[/TD]
[TD="width: 64"]b_postal[/TD]
[TD="width: 64"]b_country[/TD]
[TD="width: 64"]avs_response[/TD]
[TD="width: 64"]cvd_response[/TD]
[TD="width: 64"]spp_customer_code[/TD]
[TD="width: 64"]spp_account_ref[/TD]
[TD="width: 64"]trn_currency[/TD]
[TD="width: 64"] processed_by
[/TD]
[TD="width: 64"]terminal_id[/TD]
[TD="width: 64"]authorizing_merchant_id[/TD]
[TD="width: 64"]cavv_response[/TD]

[TD="colspan: 2"]11-----------[/TD]

</tbody>

The 54JN column tells me what location the transaction was processed by with the first two numbers. Unedited this gives the number of the location followed by the employee who processed it.

This converts to (on the summary page and the location page:

2018-08-22 06:46:45.423	D--------	1464	-	54JN

<colgroup><col style="mso-width-source:userset;mso-width-alt:2998;width:62pt" width="82"> <col style="mso-width-source:userset;mso-width-alt:2011;width:41pt" width="55"> <col style="mso-width-source:userset;mso-width-alt:6363;width:131pt" width="174"> <col style="mso-width-source:userset;mso-width-alt:9764;width:200pt" width="267"> <col style="mso-width-source:userset;mso-width-alt:2450;width:50pt" width="67"> <col style="mso-width-source:userset;mso-width-alt:4242;width:87pt" width="116"> <col style="mso-width-source:userset;mso-width-alt:3291;width:68pt" width="90"> <col style="mso-width-source:userset;mso-width-alt:7424;width:152pt" width="203"> <col style="mso-width-source:userset;mso-width-alt:4022;width:83pt" width="110"> <col style="mso-width-source:userset;mso-width-alt:3876;width:80pt" width="106"> <col style="mso-width-source:userset;mso-width-alt:6144;width:126pt" width="168"> </colgroup><tbody>
[TD="class: xl66, width: 82"]Print?
[/TD]
[TD="class: xl67, width: 679, colspan: 5"]Branch[/TD]
[TD="class: xl68, width: 90"]54[/TD]
[TD="class: xl73, width: 203"]--[/TD]
[TD="class: xl73, width: 110"][/TD]
[TD="class: xl73, width: 106"][/TD]
[TD="class: xl74, width: 168, align: right"]August 22, 2018[/TD]

[TD="class: xl66"]P[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]

[TD="class: xl66"]P[/TD]
[TD="class: xl70"]Branch[/TD]
[TD="class: xl70"]trn_datetime[/TD]
[TD="class: xl70"]trn_card_owner
[/TD]
[TD="class: xl70"]trn_type
[/TD]
[TD="class: xl71"]Amount
[/TD]
[TD="class: xl70"]trn_amount[/TD]
[TD="class: xl70"]trn_order_number[/TD]
[TD="class: xl70"]trn_auth_code[/TD]
[TD="class: xl70"]trn_card_type[/TD]
[TD="class: xl70"]processed_by[/TD]

[TD="class: xl66"]P[/TD]
[TD="class: xl66"]54[/TD]

[TD="class: xl66"]P
[/TD]
[TD="class: xl72"] $14.64
[/TD]

[TD="class: xl66"]-[/TD]
[TD="class: xl66"]VI[/TD]

</tbody>

I keep wondering if there would be a simpler way of doing this. Instead of copying the entire raw data to each location's page 4 times and using the If statement to determine what lines to print would there be a macro that would filter it from the raw data to give me the same results but it would only put what belongs to each location on the location page. The co-worker that built this built in some future proofing by having the potential for over 800 lines per type of payment under each branch when we currently are in the 300-400 range. So, each location tab has nearly 3400 lines of data and the summary page is currently at 128,938 lines of data even though only 659 are printed on the summary page for today's transactions.

 

[mse330]

I have some questions:

• Are you exporting the raw data from a system or having it sent to you in excel all in one sheet?
• Are you manually copying & pasting the data related to each location ? It is based on the first 2 digits of "processed by" field ? What if the location number is 1 ? It shows 01 ?
• Why do you copy the same data to each location page 4 times ?
• If you have your data all in 1 place initially, why don't you create a Pivot Table based on ?

 

[DaleRW]

1. Yes, I am exporting from a system into Excel all on one sheet.
2. No, the data is automatically input into each sheet. There is a hidden Data sheet where the raw data automatically copies starting in column B. In Column A is a formula to pull out the first two digits of the "processed by" field. We only have one user for location 01 and when she was set up the location number at the start of her name is 01.
3. The way my co-worker set up the workbook it copies the data on each locations page for each possible payment type (there are 4). So it copies the data into 4 different sections and then formulas determine if a payment in that section fits that type of payment and is for that location.
4. A pivot table won't work from my experience with pivot tables as the processed by is each individual user of the system and for all locations except 1 there are multiple users. So I can't just pull all the data for location 54 without first removing the 54 from the processed by user. I use pivot tables for some things but with this I just get a big mess and nothing close to what I want.

The other thing is that I am having my team process this daily and their experience with and knowledge of excel is extremely limited so I was trying to find a way to get this as automatic as possible.

 

[mse330]

Thanks DaleRW for the clarification … Now just going back to our original code, I have made a slight adjust to un-filter the sheets first then apply the filter again perhaps this will solve the issue of running the macro twice. If it doesn't, try to run the code from the VBE by pressing F5 & check if it works

Sub FilterSheet_Updated()
Dim ws As Worksheet
For Each ws In Worksheets
    Select Case ws.Name
        Case "(01)", "(03)", "(04)", "(07)", "(08)", "(09)", "(10)", "(12)", _
             "(13)", "(14)", "(15)", "(16)", "(17)", "(19)", "(21)", "(23)", _
             "(28)", "(29)", "(30)", "(31)", "(33)", "(34)", "(35)", "(36)", _
             "(38)", "(39)", "(40)", "(51)", "(52)", "(53)", "(54)", "(55)", _
             "(56)", "(57)", "(58)", "(59)", "(60)", "(62)"
            ws.AutoFilterMode = False
            ws.Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:="P"
        Case "Summary"
            ws.AutoFilterMode = False
            ws.Range("A2").CurrentRegion.AutoFilter Field:=1, Criteria1:="P"
    End Select
Next
End Sub

 

[DaleRW]

Thanks again for your response. This change caused a strange situation where they it caused lines without a P to be visible on all pages and when I went into the filter button to tell it to only show the lines with a P in column A it wouldn't change what is showing and in turn what would be printed or show on the PDF.