VBA Macro issue.

[El Scampio]

Hi

Running Excel 2007 on XP.

I kindly was helped in recording a Macro for a simple sort function. The Macro will work initially but after that will constantly fail.

It takes me to debugging in VBA which I have no idea about.

This is the macro:

Sub Sort()
'
' Sort Macro
'

'
Range("C6:P106").Select
ActiveWindow.SmallScroll Down:=-111
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("P6:P106") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("C6:P106")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

The part that is Red and Bold is highlighted yellow on VBA so I gather that is where the problem lies.

Basically all I want the Macro to do is highlight cells: C6 to P106 and sort from largest to smallest by column P.

I have re-recorded the macro in hope it would fix it but the same issue repeats every time.

Any help would be greatly appreciated.

Thanks

Scamp

 

[jasonb75]

Scamp,

2 possible causes (maybe others that I haven't thought of)

1) The name of the sheet has been changed, Sheet1 is now called something else.

2) Sheet 1 is not the active sheet (the sheet that is visible when you run the macro). Your macro looks as if it should run from any sheet, but I've had the same problem previously.

If that doesn't solve it, what error message are you getting?

 

[El Scampio]

Scamp,

2 possible causes (maybe others that I haven't thought of)

1) The name of the sheet has been changed, Sheet1 is now called something else.

2) Sheet 1 is not the active sheet (the sheet that is visible when you run the macro). Your macro looks as if it should run from any sheet, but I've had the same problem previously.

If that doesn't solve it, what error message are you getting?

Thank you you nailed it.

I had remaned the worksheet from "Sheet1" to "Planner". I have changed the "Sheet1" parts to "Planner" and now works great.

Thanks for you help.

 

[jasonb75]

I couldn't leave you with that nasty recorded macro, this might not be the best but it's an improvement

Sub Sort()
Worksheets("Planner").Select
Range("C6:P106").Select
Selection.Sort Key1:=Range("P6"), Order1:=xlDescending, MatchCase:=False, _
Header:=xlGuess, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End Sub

Will you always be using C6:P106, or does the range vary in size?

Jason