Sort Marco won't work in 2010
 

My office has just converted us to excel 2010. My lovley spreadsheet macro that made me the envy of the office no longer works and everyone now wants my blood.

The sheet is protected.

I have a macro that will select a variable amount of rows and sort alphabetically. It looks for the hidden cell xdummy then selects every row above it up to row 13, then sorts A -Z. Except now it doesn't. Help!

Sub Sort()
'
' Sort Macro
'

'Sheets("Cashflow").Unprotect "Cashflow2012"

Cells.Find(What:="xdummy", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate


Rows("13:" & ActiveCell.Row - 1).Select
Selection.Sort Key1:=Range("a13"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Sheets("Cashflow").Protect "Cashflow2012"

End Sub

Sort Marco won't work in 2010
 

On Friday, August 31, 2012 8:01:35 AM UTC-7, GSENSEI wrote:
My office has just converted us to excel 2010. My lovley spreadsheet

macro that made me the envy of the office no longer works and everyone

now wants my blood.



The sheet is protected.



I have a macro that will select a variable amount of rows and sort

alphabetically. It looks for the hidden cell xdummy then selects every

row above it up to row 13, then sorts A -Z. Except now it doesn't.

Help!



Sub Sort()

'

' Sort Macro

'



'Sheets("Cashflow").Unprotect "Cashflow2012"



Cells.Find(What:="xdummy", After:=ActiveCell, LookIn:=xlFormulas,

LookAt _

:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _

False, SearchFormat:=False).Activate





Rows("13:" & ActiveCell.Row - 1).Select

Selection.Sort Key1:=Range("a13"), Order1:=xlAscending, Header:=xlGuess,

_

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal



Sheets("Cashflow").Protect "Cashflow2012"



End Sub









--

GSENSEI

Give this a try, from excel 2010.

Option Explicit
Option Compare Text
Sub ASort()

'Sheets("Cashflow").Unprotect "Cashflow2012"
Dim i As Integer
Dim j As Integer
Dim k As Integer

Cells.Find(What:="xdummy", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

i = ActiveCell.Row
j = 13
k = i - j

ActiveCell.Offset(-(i - j - 1), 0).Resize(k - 1, 1).Select
Selection.Sort Key1:=Range("a13"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'Sheets("Cashflow").Protect "Cashflow2012"

End Sub

HTH
Regards,
Howard

Sort Marco won't work in 2010
 

On Friday, August 31, 2012 10:01:35 AM UTC-5, GSENSEI wrote:
My office has just converted us to excel 2010. My lovley spreadsheet

macro that made me the envy of the office no longer works and everyone

now wants my blood.



The sheet is protected.



I have a macro that will select a variable amount of rows and sort

alphabetically. It looks for the hidden cell xdummy then selects every

row above it up to row 13, then sorts A -Z. Except now it doesn't.

Help!



Sub Sort()

'

' Sort Macro

'



'Sheets("Cashflow").Unprotect "Cashflow2012"



Cells.Find(What:="xdummy", After:=ActiveCell, LookIn:=xlFormulas,

LookAt _

:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _

False, SearchFormat:=False).Activate





Rows("13:" & ActiveCell.Row - 1).Select

Selection.Sort Key1:=Range("a13"), Order1:=xlAscending, Header:=xlGuess,

_

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal



Sheets("Cashflow").Protect "Cashflow2012"



End Sub









--

GSENSEI
try
Sub Sort()

with Sheets("Cashflow")
..Unprotect "Cashflow2012"
set mf=.Cells.Find(What:="xdummy",lookIn:=xlFormulas,L ookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
if not mf is nothing then
mr=mf.row
..Rows("13:"mr).Sort Key1:=.Range("a13"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
end if
..Protect "Cashflow2012"
end with
End Sub

if that doesn't work send this msg and
file to dguillett1 @gmail.com

Hi folks,

I've tried both suggestions but neither one is working still. I can't send the actual sheet out due to office policy. But to make things worse it turns out we can't even sort manually when sheet is protected.

I can get the protection to turn off and on in the macro that is fine. Its now my ability to sort that is the problem
It seems to have no problem findind and selceting the correct range its just the sorting that gives issues.

this bit is in yellow.

Selection.Sort Key1:=Range("a13"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Any ideas?

Sort Marco won't work in 2010
 

On Friday, August 31, 2012 10:01:35 AM UTC-5, GSENSEI wrote:
My office has just converted us to excel 2010. My lovley spreadsheet

macro that made me the envy of the office no longer works and everyone

now wants my blood.



The sheet is protected.



I have a macro that will select a variable amount of rows and sort

alphabetically. It looks for the hidden cell xdummy then selects every

row above it up to row 13, then sorts A -Z. Except now it doesn't.

Help!



Sub Sort()

'

' Sort Macro

'



'Sheets("Cashflow").Unprotect "Cashflow2012"



Cells.Find(What:="xdummy", After:=ActiveCell, LookIn:=xlFormulas,

LookAt _

:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _

False, SearchFormat:=False).Activate





Rows("13:" & ActiveCell.Row - 1).Select

Selection.Sort Key1:=Range("a13"), Order1:=xlAscending, Header:=xlGuess,

_

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal



Sheets("Cashflow").Protect "Cashflow2012"



End Sub









--

GSENSEI
In mine, try changing
Rows("13:"mr).Sort Key1:=.Range("a13"), _

to
Rows("13:"mr).Sort Key1:=.cells(mf.row,"a"), _

GSENSEI[/i][/color]
In mine, try changing
Rows("13:"mr).Sort Key1:=.Range("a13"), _

to
Rows("13:"mr).Sort Key1:=.cells(mf.row,"a"), _[/quote]


Still doesn't like it. god i hate 2010!

(this seems fine)

Set mf = .Cells.Find(What:="xdummy", LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not mf Is Nothing Then
mr = mf.Row

This highlights in red

Rows("13:"mr).Sort Key1:=.cells(mf.row,"a"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If


And I get a Complie error message.