Excel-Frequently used macro? Automating?

I am new to the forum.
I have a question on editing frequently used macros. Below is the macro which I use. The rows in bold are the rows that I have to manually edit after macros runs each time. I am wondering how to set up macros so that after it runs it automatically goes in & changes the sort row, highlight row & paste row.
The first row (sort row) goes down one number after each macro (i.e.-185 to 184...184 to 183....).
The second row (highlight row) goes down one number after each macro (i.e.-184 to 183...183 to 182...).
The third row (paste row) goes down 3 numbers after each macro (i.e.-887 to 884...884 to 881....).
Can anyone give me any suggestions on how to automate this, so that I don't manually have to change the sort criteria after each macro? Here is the macro:

ActiveWindow.ScrollRow = 153
ActiveWindow.ScrollRow = 138
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 1
Rows("1:336").Select
Selection.Sort Key1:=Range("A 185"), Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 176
ActiveWindow.ScrollRow = 175
ActiveWindow.ScrollRow = 174
Range("A184:E184").Select
Selection.Interior.ColorIndex = 35
ActiveWindow.ScrollRow = 171
ActiveWindow.ScrollRow = 170
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 1
Range("A1:E1").Select
Selection.Copy
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 856
ActiveWindow.ScrollRow = 860
Range("B887:F887 ").Select
ActiveSheet.Paste
End Sub

My operating system is WindowsXP & I use OfficeXP (ExcelXP). Thanks in advance for any help/suggestions

Hi,

What exactly does the macro do? I can't really tell by looking at your code. How many times does it run? you could put part of your macro in a loop and have a counter that increments or decrements each time it runs.



You can use something like the code above to change your "A 185" reference each time the code loops. The second time through the loop ("A" & counter) is equivalent to ("A184") . I hope this helps.

Chuck

galaganut-
Thanks for the reply. Here is the code I ended up coming up with. It works great!

Sub Try()
Dim i1 As Long, i2 As Long, i3 As Long
i1 = Range("BJ1")
i2 = Range("BK1")
i3 = Range("BL1")
Rows("1:336").Sort Key1:=Range("A" & i1), Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
Range("A" & i2 & ":E" & i2).Interior.ColorIndex = 35
Range("A1:E1").Copy Range("B" & i3 & ":F" & i3)
Range("BJ1") = i1 - 1
Range("BK1") = i2 - 1
Range("BL1") = i3 - 3
End Sub