Solved: Sort Macro

[sooty8]

Hi All

Recorded the Macro below if I insert a row manually into the sheet do I have to open the macro and change the range or can it find the new range automatically.


Sub Macro1()
'Macro1 Macro
Range("A1:H17").Select
ActiveWorkbook.Worksheets(" February 2013").Sort.SortFields.Clear
ActiveWorkbook.Worksheets(" February 2013").Sort.SortFields.Add Key:=Range( "A2:A17"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(" February 2013").Sort
.SetRange
Range("A1:H17")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub


Many Thanks For Any Help
Regards
Sooty8

[mancubus]

hi,
try this:


Sub SortRange()
Dim rng As Range
Dim LastRow As Long

With Worksheets("February 2013")
LastRow = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
Set rng = .Range("A1:H" & LastRow)
rng.Sort Key1:=.Range("A2"), Order1:=xlDescending, Header:=xlYes
End With
End Sub

[sooty8]

Hi Mancubus

Thanks for the reply inserted your macro receiving an error message
Runtime Error 9
Subscript out of range

On this line - With Worksheets("February 2013")
Any further help much appreciated.
Sooty8

[mancubus]

from your recorded macro...
ActiveWorkbook.Worksheets(" February 2013")

Change
Worksheets("February 2013")

to
Worksheets(" February 2013")


or correct the worksheet name by removing extra spaces...

[sooty8]

Hi Mancubus

My apologies there was an extra space on the sheet tab.
Thanks again worked perfectly just what I needed.

Regards

Sooty8