Macro crashes on sort (excel 2007)

[Dr_Phill]

Hi. I'm trying to record a relatively simple macro to sort and transpose a set of values (among other things). I've saved the macro in my personal workbook, but it crashes when it hits the sort command every time. Commands prior to the sort all work. I've recorded similar macros in older versions of excel without issue. Please help, I have hundreds of spreadsheets to process!

The visual basic code for the macro is below... The debugger shows it crashing on the line "ActiveWorkbook.Worksheets("Pnumber203correct").sort.SortFields.Clear"

Sub sort()
'
' sort Macro
'
' Keyboard Shortcut: Ctrl+t
'
Range("B2").Select
Application.CutCopyMode = False
Selection.Copy
Range("B1").Select
Application.CutCopyMode = False
Selection.Copy
Range("C4").Select
ActiveSheet.Paste
Range("B2").Select
Application.CutCopyMode = False
Selection.Copy
Range("D4").Select
ActiveSheet.Paste
Range("E4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=IF(R[-1]C[-3]=""male"",1,2)"
Range("F7").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-4]=""Correct"",RC[-3]:RC[-3],"""")"
Range("F7").Select
Selection.AutoFill Destination:=Range("F7:F27"), Type:=xlFillDefault
Range("F7:F27").Select
Range("A7:F27").Select
ActiveWorkbook.Worksheets("Pnumber203correct").sort.SortFields.Clear
ActiveWorkbook.Worksheets("Pnumber203correct").sort.SortFields.Add Key:=Range _
("A7:A27"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Pnumber203correct").sort
.SetRange Range("A7:F27")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("A:A").ColumnWidth = 14.57
Range("F8").Select
Selection.Cut Destination:=Range("F4")
Range("F9").Select
Selection.Cut Destination:=Range("G4")
Range("F7").Select
Selection.Cut Destination:=Range("H4")
Range("H4").Select
End Sub

 

[dave3009]

Probably because you have called your macro 'sort', try a different name such as 'pSort'

 

[Dr_Phill]

Thanks for replying, but this is only my latest attempt and I have named the macro in various different ways in previous attempts with the same result. I'd really appreciate any other suggestions?

 

[dave3009]

Is there a sheet called Pnumber203correct in the activeworkbook and is it protected?

 

[Dr_Phill]

Yes, Pnumber203correct is the name of the active worksheet that I'm trying to run the macro in. The macro was recorded in a different worksheet (with the same format) and saved to my personal workbook so that it could be applied to different sheets in different sessions. None of the sheets or elements are protected. All of the commands in the macro before the sort apply successfully, but it crashes there.

 

[dave3009]

I cleaned up the code and it seemed to execute OK.

Sub pSort()
'
' sort Macro
'
' Keyboard Shortcut: Ctrl+t
'

Range("B1").Copy Range("C4")
Range("B2").Copy Range("D4")
Range("E4").FormulaR1C1 = "=IF(R[-1]C[-3]=""male"",1,2)"
Range("F7:F27").FormulaR1C1 = "=IF(RC[-4]=""Correct"",RC[-3]:RC[-3],"""")"
ActiveWorkbook.Worksheets("Pnumber203correct").sort.SortFields.Clear
ActiveWorkbook.Worksheets("Pnumber203correct").sort.SortFields.Add Key:=Range _
("A7:A27"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Pnumber203correct").sort
.SetRange Range("A7:F27")
.Header = xlGuess
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("A:A").ColumnWidth = 14.57
Range("F8").Cut Range("F4")
Range("F9").Cut Range("G4")
Range("F7").Cut Range("H4")

End Sub

 

[Dr_Phill]

Thank you so much! I've left my office for the day, but I'll try this first thing in the morning and let you know how it goes!