Hello. I'm new to this forum, but an avid power Excel user for many years. However, I'm not very adept with VBA syntax...though I did figure out how to automate autofiltering when a workbook is first opened using the Activate and ApplyFilter commands. What I could really use some help with is a script that will allow us to sort locked/protected/autofiltered cells (a well documented challenge) containing formulas that are linked to data in an external "master" spreadsheet. In other words, prevent users from being able to edit cells with dynamic formulas, yet still allow them to sort the autofiltered results.
I have searched high and low and can't quite find enough info to build this script that's needed on my own. I have come up with a couple sets of code that come close, but lack the combination of: 1. working with an autofiltered data set (vs whole columns of data in large 50MB+ files) AND 2. being "portable" enough to be invoked on the active column header (vs having to use a discrete range for every column header in every worksheet). Here's what I have so far on those two fronts for ascending sorts (descending will be simple enough once this is worked out, though it would be nice to have a single routine that does both alternatively by, say, repeatedly clicking on the column header assigned to a macro).
1. code that works with the active cell column, but without regard to the autofilter ranges:
Sub SortAsc()
ActiveSheet.Unprotect
ActiveCell.EntireColumn.Select
Selection.Sort Key1:=ActiveCell, Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowSorting:=True
End Sub
2. code that works with the autofilter ranges, but would require a separate subroutine for every column header in every worksheet (since the range of rows varies from worksheet to worksheet):
Sub SortAsc()
ActiveSheet.Unprotect
ActiveSheet.AutoFilter.Sort.SortFields.Add Key:=Range("B1:B15000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowSorting:=True
End Sub
Can someone please help me merge these two partial solutions together to come up with a macro/script that can be used to sort with the autofilters for any column in any worksheet in the active workbook? I think I'm really close, but just couldn't find enough info on the syntax AutoFilter.Sort/Selection.Sort objects.
Thanks!
Bookmarks