Macro code
I am trying to automate manual procedures on a spreadsheet - things like
deleting certains fields, rows, putting in new values for certain things etc (there are quite a few stages to this). As you can see in my macro code below it makes references to the specific fields and cells i deleted or renamed etc. Is there a way that this can be altered in any way that the macro still works even if a new set of data is pasted over the old? this means that the macro must b able to adjust to the changing cell refs, the number of cells there are etc. i want this macro to work on any sheet of the same kind - obviously they are all dated differently and contain sometimes less or more data but how can the macro be made aware of this? i dont want to have to re-code the macro everytime. Also what can i do so the macro is accessible all the time on any machine i use and even when i re-open the file after having closed it, because at the moment when i close down that particular sheet the macro disappears! I have the following code: selection.AutoFilter Field:=13, Criteria1:="21 India" Rows("123:1902").Select Range("G123").Activate Selection.ClearContents ActiveWindow.SmallScroll Down:=-15 Selection.AutoFilter Field:=13, Criteria1:="14 Excluded from Headcount" Rows("21:1786").Select Range("G1786").Activate Selection.ClearContents ActiveWindow.SmallScroll Down:=-51 ActiveWindow.ScrollRow = 897 ActiveWindow.ScrollRow = 870 ActiveWindow.ScrollRow = 837 ActiveWindow.ScrollRow = 820 ActiveWindow.ScrollRow = 797 ActiveWindow.ScrollRow = 770 ActiveWindow.ScrollRow = 740 ActiveWindow.ScrollRow = 700 ActiveWindow.ScrollRow = 664 ActiveWindow.ScrollRow = 624 ActiveWindow.ScrollRow = 584 ActiveWindow.ScrollRow = 540 ActiveWindow.ScrollRow = 497 ActiveWindow.ScrollRow = 414 ActiveWindow.ScrollRow = 371 ActiveWindow.ScrollRow = 331 ActiveWindow.ScrollRow = 291 ActiveWindow.ScrollRow = 254 ActiveWindow.ScrollRow = 217 ActiveWindow.ScrollRow = 114 ActiveWindow.ScrollRow = 81 ActiveWindow.ScrollRow = 54 ActiveWindow.ScrollRow = 28 ActiveWindow.ScrollRow = 4 ActiveWindow.ScrollRow = 1 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 9 Selection.AutoFilter Field:=13 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Selection.AutoFilter Field:=3, Criteria1:="VACANCY" Rows("1443:1483").Select Selection.ClearContents ActiveWindow.SmallScroll Down:=-3 Range("B1:C1").Select Range("C1").Activate Selection.AutoFilter Field:=3 Selection.AutoFilter Field:=6, Criteria1:="Microsoft" Rows("1727:1741").Select Selection.ClearContents Selection.AutoFilter Field:=6, Criteria1:="Hedra" Rows("1489:1707").Select Selection.ClearContents Selection.AutoFilter Field:=6, Criteria1:="iSOFT" Rows("1592:1770").Select Selection.ClearContents Selection.AutoFilter Field:=6, Criteria1:="Red Tray" Rows("1414:1755").Select Selection.ClearContents Selection.AutoFilter Field:=6, Criteria1:="AMS" Rows("1687:1738").Select Selection.ClearContents Selection.AutoFilter Field:=6 Selection.AutoFilter Field:=6, Criteria1:="System C" ActiveWindow.SmallScroll ToRight:=18 ActiveWindow.ScrollColumn = 18 ActiveWindow.ScrollColumn = 17 ActiveWindow.ScrollColumn = 16 ActiveWindow.ScrollColumn = 15 ActiveWindow.ScrollColumn = 14 ActiveWindow.ScrollColumn = 13 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.SmallScroll Down:=-15 Range("G1502").Select ActiveCell.FormulaR1C1 = "AP" With ActiveCell.Characters(Start:=1, Length:=2).Font .Name = "Arial" .FontStyle = "Regular" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Range("G1502").Select Selection.Copy Range("G1515:G1706").Select ActiveSheet.Paste Range("G1516").Select Application.CutCopyMode = False End Sub thanks alot. |
All times are GMT +1. The time now is 09:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com