Ben

I think this does what you have asked for; give it a go (on a copy of the
data)

Sub Test3()

Dim CopyCount As Long
Dim i As Long

CopyCount = InputBox("Please input the number of times to loop", "Copy
Counter", 1)

For i = 0 To CopyCount - 1

Rows("1:336").Sort Key1:=Range("A185").Offset(-i, 0), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlLeftToRight

Range("A184:E184").Offset(-i, 0).Interior.ColorIndex = 35
Range("A1:E1").Copy Range("B887:F887").Offset(-3 * i, 0)

Next 'i

End Sub

I don't know how you determine how many times the sort and copy runs so I've
used an InputBox.

Regards

Trevor


"Ben Kingsley" wrote in message
...
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.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/