Dear All, I am using the coding

[robcarr]

Dear All,

I am using the coding below to sort a pivot table, by finding a unique word inthe table and then moving 2 cells to the right and then sorting the data, how can i stop the coding from selecting the same cell in the sort each time, and to actually select the cell 2 cells to the right of the find.

Sheets("Bill Spec by Prod&quot.Select
'
Cells.Find(What:="Rob Carr", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False).Activate
ActiveCell.Offset(0, 2).Activate
Selection.Sort Key1:="R6C3", Order1:=xlDescending, Type:=xlSortValues, _
OrderCustom:=1, Orientation:=xlTopToBottom
Cells.Find(What:="Robert Carr", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Activate
ActiveCell.Offset(0, 2).Activate
Selection.Sort Key1:="R36C3", Order1:=xlDescending, Type:=xlSortValues, _
OrderCustom:=1, Orientation:=xlTopToBottom
End Sub

the area that is causing the problem is this

Key1:="R6C3", in the find, it is always going to sort on row 6 column 3, but the top of the data could be in a different place, how can i get the key1:= to equal where the active cell ends up.

hope this makes sense,

Rob.

 

[Loomah]

hi rob
I don't nderstand your first question! By using offset you are activating the cell 2 to the right of the find?

Definately don't understand the bit about selecting the same cell each time...unless your in a loop for some reason?

<<how can i get the key1:= to equal where the active cell ends up.>>

Key1:=ActiveCell

I've a feeling I'm missing the point a lot today, incase I've misunderstood.
;-)
If a man says something and there are no women there to hear him, is he still wrong?