Macro code

Z

Zak

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top