AutoFiltering Multiple Sheets?

There's a conference coming up and I have to track the Registration forms.

I have 4 sheets in an excel sheet. Sheet 1 has the Last and First name and contact information of registrants. Sheet 2-4 links to the Last and First Name Columns of Sheet 1 then continues with different information. (Sheet 2 is Meals and Room, Sheet 3 is financials, Sheet 4 is Workshops)

This is all well and fine, and I've linked cells in Financials to auto-tabulate their bill using information from Meals and Room. It's all rather cool!

However, it will eventually be that the Registrants in Sheet 1 will become overwhelming and confusing. I have an Auto-Filter set up in the top row to re-sort Sheet 1 by Last Name from time to time.

When I ask Auto-Filter to do this, then Sheet 1 properly re-orders itself by Last Name. The linked cells in sheets 2-4 also do this accordingly ... but not the rest of the information in these sheets.

Is there a way to link the rows in Sheets 2-4 to the Auto-Filter command in Sheet 1?

If you wanted to Use Auto Filter to sort the sheets, a simple VBA code could work for you,

Please Login or Register  to view this content.

Originally Posted by SargeAbernathy

What if I have a sheet I don't want to be affected by AutoFilter?

This will work

Please Login or Register  to view this content.

hi Sarge,

I think that Dave's code in post number 4 will have problems if there are any completely blank rows or columns within each of the spreadsheets because the use of "With WS.Range("A1")" rather than the complete range means that Excel will "guess" what to sort using the current region.

Before Dave's code will do anything, you have to make the macro start somehow, for example when you push a button on sheet 1 after making your changes. However from some experiences I've had when sorting data with formulae within the range, I think you may find you still have problems due to the formulae references being messed up...

Can you please attach a sample workbook (with no confidential data) for us to investigate?
(hopefully in a PC compatible format or I can't help)

Dave
Just curious, why have you dimmed the ws as an object rather than a worksheet (is it something to do with the object model in Mac's)?

Rob

Yes, it should have been worksheet not object...