name a range or cell so macro stays consistant

[jimmyyy]

To sum it up i make a macro then months later end up inserting a column or two in the worksheet, and the macro no longer associates with the field, but stays on the range or cell. For example the macro is filtering say column ?H:H? which is fine but if i insert a new column in were column ?D? is, the macro no longer works. It tays on ?H:H? but the field has moved over one ?I:I?. Am i supposed to name give the range?

MY 2 YEAR ITCH:moosegrin

a very basic problem i cant figure out. For quite some time ive yet to discover, is been a itch ive yet to scratch.
In Excel VB I CREATE A MACRO (many), Which works great. But changes in business thought the yr push me to add additional columns thought the yr. Well in excel I have to tag them on to the end of my database field in order to not mess up the macro. But they usually don?t go well at the end of the data field, but need to group up next to like fields, I think its just a matter of naming the cells, ranges, or fields? Hears a super simple example below, pardon my baby steps.

Sub Filter_n_Sum()

Selection.AutoFilter Field:=8, Criteria1:="JM" ' THIS FILTERS COLUMN 'H' IN EXCEL( this ?Field:8? creates a problem later)
Cells.Select
Selection.Copy
Sheets("Stats").Select
Range("A1").Select
ActiveSheet.Paste
Range("J2:J58").Select
Range("J58").Activate ' THIS SELECTS THE RANGE TO SUM , ( AGAIN HERE I WOULD RATHER GIVE THE RANGE OF CELLS some type of refferance.)
ActiveCell.FormulaR1C1 = "=SUM(R[-56]C:R[-1]C)"
Columns("J:J").ColumnWidth = 10.5 ' HOW DO I SET "J:J" TO = A NAME, THEN LATER IF I DESIRED I COULD INSERT A NEW COLUMN or FIELD i.e. WERE COLUMN B WAS, IT will push all the fields down but that WOULDNT EFFECT THE MACRO BECUASE ITS ATTACHED TO A NAME or reference INSTEAD OF column "J:J" OR IN THE CASE OF THE FILTER 'Field:=8'

End Sub


' ANY HELP IN LAYMANS TERMS IS HUGLY APPRECIATED.
ps this is my first post hopefully its accurate
pss what is word perfect anyways?:rofl:

[Bob Phillips]

In Excel, there is a facility called 'Defined Names'. Defined Names refer to a range of cells (they can actually refer to constants or formulae, but ignore that for now), and have the advantage that you seek, if the range being referred to moves, the name is updated to point to the new range.

So, if the name points at H5:M10 and you insert a row at 1, the name then points at H6:M11. If you insert a row at 6, it then points to H5:M11. advantage with columns.

To create a name, select the range that you want to name (it can be complete rows or complete columns), goto Insert>Name>Define..., type a name, and you should see your range defined in the RefersTo box.

You use your name in an Excel formula as you would a cell reference or range of cells, e.g. = SUM(myRange).

In a formula you use it it the same way,

Range("myRange").ColumnWidth = 10.5

note the quotes just as you would with a range reference.

[jimmyyy]

xld thank you soooooo much, i new it was simple but never thought to ask online, this will make a HUGE diff in my excell apps that get about 4 hrs of use a day from more than 1 person.

i wasnt sure if theese forums worked, i tried this exact post on ozgrid.com & they deleated it bothtimes thoose uptight pricks.

anyways, thank you. tuns.:rofl:

[Bob Phillips]

xld thank you soooooo much, i new it was simple but never thought to ask online, this will make a HUGE diff in my excell apps that get about 4 hrs of use a day from more than 1 person.

i wasnt sure if theese forums worked, i tried this exact post on ozgrid.com & they deleated it bothtimes thoose uptight pricks.

anyways, thank you. tuns.:rofl:

I wouldn't use that self-same language, but I do agree that OzGrid is over-zealous in their application of the forum rules.

I have made that same point to the admin guys on numerous occasions, but obviously I carry a lot of influence as they just got stricter.

[jimmyyy]

well i wouldnt hesitate to to ad lip.

Xld, thanks again for your your help.