On 2002-03-08 11:35, JohnJay wrote:
Barrie, I am getting your idea, and I think it may work. Couple of questions though...
How can I lock the header, and the following part of your code is giving me an error in VBA
SortColumn = ActiveSheet.Find(what:="Division", _
LookAt:=xlWhole).Column
Now that I think about it, you won't be able to lock the header and allow the user to insert a column. I was thinking you could lock the header row and then protect the worksheet, but if you protect the worksheet the user won't be able to insert a column. How about slightly changing the code to:
Sub SortData()
Dim SortColumn As Integer, LastColumn As Integer
Dim LastRow As Long
LastColumn = Range("A1").End(xlToRight).Column
LastRow = Range("A65536").End(xlUp).Row
On Error GoTo ErrorHandler
SortColumn = ActiveSheet.Find(what:="Division", _
LookAt:=xlWhole).Column
On Error GoTo 0
Range(Cells(1, 1), Cells(LastRow, LastColumn)).Sort _
Key1:=Range("A" & SortColumn), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Exit Sub
ErrorHandler:
MsgBox prompt:="Could not find a header labelled DIVISION" _
& Chr(13) & "Data was not sorted", _
Buttons:=vbCritical + vbOKOnly
End Sub
This will sort on "Division" and, if it's not found, will return an error message.
As for the error message you received, does "Division" exist in the spreadsheet. The macro is searching for an exact match to that word.
Regards,