Issues with Personal View filtering and running Macros(VBA)

%3CLINGO-SUB%20id%3D%22lingo-sub-1595877%22%20slang%3D%22en-US%22%3EIssues%20with%20Personal%20View%20filtering%20and%20running%20Macros(VBA)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1595877%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20use%20a%20shared%20Excel%20365%20(co-authored%20desktop)%20file%20to%20track%20our%20work%20orders%2C%20the%20data%20from%20our%20ERP%20system%20is%20updated%20by%20the%20users%20multiple%20times%20during%20the%20day.%20They%20paste%20the%20new%20ERP%20data%20into%20blank%20%E2%80%9Cupdate%E2%80%9D%20tab%2C%20go%20to%20their%20current%20working%20tab%2C%20then%20click%20a%20%E2%80%9CRun%20Update%E2%80%9D%20button%20that%20triggers%20an%20update%20macro%20that%20runs%20and%20updates%20their%20current%20working%20tab%20data.%3C%2FP%3E%3CP%3EThis%20has%20been%20working%20relatively%20well%2C%20but%20now%20we%20are%20having%20an%20issue%20with%20the%20%E2%80%9Csheet%20view%E2%80%9D%20option%20that%20was%20added%20with%20Microsoft%20365%20update%20%3CEM%3EVersion%202007%20(Build%2013029.20308).%3C%2FEM%3E%3C%2FP%3E%3CP%3EThe%20macro%20uses%20the%20code%20%E2%80%9C%3CEM%3EActiveSheet.AutoFilter.ShowAllData%22%20%3C%2FEM%3E%26nbsp%3Bto%20clear%20the%20filters%20before%20the%20update%20runs.%20But%2C%20this%20code%20does%20not%20remove%20the%20filter%20of%20any%20user%20that%20is%20has%20%E2%80%9Csheet%20view%E2%80%9D%20set%20to%20%E2%80%9Cpersonal%20view%E2%80%9D%2C%20that%20users%20view%20does%20not%20change%20and%20somehow%20that%20carries%20over%20into%20the%20update%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20a%20big%20problem%20because%20there%20is%20no%20way%20the%20other%20users%20know%20(that%20I%20am%20aware%20of)%20if%20someone%20has%20a%20%E2%80%9Cpersonal%20view%E2%80%9D%20active%20on%20the%20worksheet.%20They%20run%20the%20update%20with%20another%20user%20in%20personal%20view%20mode%2C%20causing%20the%20update%20data%20to%20be%20flawed%2C%20missing%20rows%2C%20having%20blank%20rows%20within%20the%20data%20table%20and%20etc.%20Luckily%20the%20data%20is%20corrected%20if%20everyone%20closes%20out%20of%20%E2%80%9Cpersonal%20view%E2%80%9D%20and%20the%20update%20is%20ran%20again.%3C%2FP%3E%3CP%3EI%20don%E2%80%99t%20know%20if%20there%20is%20even%20any%20VBA%20code%20that%20would%20remove%20the%20%E2%80%9Csheet%20view%E2%80%9D%20filters%20of%20all%20users%20in%20%E2%80%9Cpersonal%20view%E2%80%9D%20of%20that%20workbook%2Fworksheet%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1595877%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

We use a shared Excel 365 (co-authored desktop) file to track our work orders, the data from our ERP system is updated by the users multiple times during the day. They paste the new ERP data into blank “update” tab, go to their current working tab, then click a “Run Update” button that triggers an update macro that runs and updates their current working tab data.

This has been working relatively well, but now we are having an issue with the “sheet view” option that was added with Microsoft 365 update Version 2007 (Build 13029.20308).

The macro uses the code “ActiveSheet.AutoFilter.ShowAllData"  to clear the filters before the update runs. But, this code does not remove the filter of any user that is has “sheet view” set to “personal view”, that users view does not change and somehow that carries over into the update data.

 

This is a big problem because there is no way the other users know (that I am aware of) if someone has a “personal view” active on the worksheet. They run the update with another user in personal view mode, causing the update data to be flawed, missing rows, having blank rows within the data table and etc. Luckily the data is corrected if everyone closes out of “personal view” and the update is ran again.

I don’t know if there is even any VBA code that would remove the “sheet view” filters of all users in “personal view” of that workbook/worksheet?

0 Replies