Sort Marco won't work in 2010

[GSENSEI]

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

The sheet is protected.

I have a macro that will select a variable amount of rows and sor
alphabetically. It looks for the hidden cell xdummy then selects ever
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 Su

 

[lhkittle]

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

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

 

[Don Guillett]

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

try
Sub Sort()

with Sheets("Cashflow")
..Unprotect "Cashflow2012"
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
..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

 

[GSENSEI]

Hi folks,

I've tried both suggestions but neither one is working still. I can'
send the actual sheet out due to office policy. But to make things wors
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 it
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

 

[Don Guillett]

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

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

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

 

[GSENSEI]

GSENSEI[/i]

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

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


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