VBA help

[Donai]

Hi, i am after a macro that will filter for JPM and RBS when the user saves the file. At present staff members are leaving filters on for various headers, but i want to be sure when the workbook is saved the data is filtered for only those two banks.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
5	Name	Group	Category	Set ID	Value Date	Entry Date	Type	Amount	CCY	Age	Source	Ref1
6		TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST	JPM	TEST
7	Joe	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST	CITI	TEST
8		TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST	JPM	TEST
9		TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST	RBS	TEST
10		TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST	BONY	TEST
11		TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST	JPM	TEST
Sheet1

 

[Smitty]

Just record a macro applying the filter you want, then call it from the BeforeSave/BeforeClose events. Although I'd probably do it from the Open event instead.

HTH,

 

[AlphaFrog]

Put this in the ThisWorkbook module.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.ScreenUpdating = False
    With Sheets("Sheet1")
        .AutoFilterMode = False
        .Range("A5:L" & Rows.Count).AutoFilter Field:=11, Criteria1:="JPM", Operator:=xlOr, Criteria2:="RBS"
    End With
    Application.ScreenUpdating = True
End Sub