Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to automate frequently used macro?
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automate Macro | Excel Discussion (Misc queries) | |||
Automate Macro | Excel Discussion (Misc queries) | |||
Automate Macro | Excel Discussion (Misc queries) | |||
Automate Macro | Excel Discussion (Misc queries) | |||
Frequently used macro-automating? | Excel Programming |