macro in 2007

[Jeanette]

Good morning
I have this macro which I have used before. I want to add information and the date to sort in column A. Row 1 os the header row. It will sort after you hit enter in the 5th column.

This was a macro I did for excel 2003 and now it doesn't work. What is wrong.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 5 And Target.Cells.Count = 1 Then
Range("Date_bought").Sort key1:=Range("a1"), order1:=xlAscending, Header:=xlYes
End If
End Sub

I've tried with row 2 blank and without, but still get the same error message

Thanks

[sdckapr]

Is the range named "Date_Bought" set to contain the range of interest including the header?

Steve

[Jeanette]

I wil attach the workbook... its kinda frustrating as I have used this macro over and over and it usually works ok.

Thanks

[sdckapr]

The range of date_bought is A2:A10. Therefore it ONLY sorts this range and assumes that A2 is the Header.

Try the line:
Range("A1").Sort key1:=Range("a1"), order1:=xlAscending, Header:=xlYes

It will use row 1 as the header and include all the columns in the sorting since they are all connected and not just sort the range A2:A10 with A2 as the header row

Steve

[Jeanette]

Did what you suggested but nothing happened... This excel 2007 is a lot better in many respects but macros are a drama..

[HansV]

If nothing happens if you use Steve's suggestion, it looks like macros are disabled. Make sure that the folder containing the workbook is a trusted location, or enable all macros.

[Jeanette]

Hi Hans
Not sure what happened, but I switched machine on and off, swore in Dutch and that did the trick :)

Thanks for all your help

[HansV]

I'm glad it's working now...!