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
macro in 2007
-
- 3StarLounger
- Posts: 392
- Joined: 25 Jan 2010, 12:21
Re: macro in 2007
Is the range named "Date_Bought" set to contain the range of interest including the header?
Steve
Steve
-
- StarLounger
- Posts: 74
- Joined: 19 Apr 2010, 22:50
Re: macro in 2007
I wil attach the workbook... its kinda frustrating as I have used this macro over and over and it usually works ok.
Thanks
Thanks
You do not have the required permissions to view the files attached to this post.
-
- 3StarLounger
- Posts: 392
- Joined: 25 Jan 2010, 12:21
Re: macro in 2007
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
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
-
- StarLounger
- Posts: 74
- Joined: 19 Apr 2010, 22:50
Re: macro in 2007
Did what you suggested but nothing happened... This excel 2007 is a lot better in many respects but macros are a drama..
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: macro in 2007
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.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 74
- Joined: 19 Apr 2010, 22:50
Re: macro in 2007
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
Not sure what happened, but I switched machine on and off, swore in Dutch and that did the trick :)
Thanks for all your help
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands