How do you use the value of a named cell in a macro in a diferent sheet, same workbook?

Question

I'm just not getting it, I'm using Excel 2003 and am totally confused as I'm jsut not getting it ...can anyone help? I need to check that named value with a number of coulumn headings on the active sheet and then insert a colum to the left of the column holding the matching text. I'm sure that bit is very hard - but I'm not even able to get started here...HELP!!

Answer 1

Well, to get your named value column (which I presume is a named range...), you would use:

ActiveWorkbook.Sheets("mySheet").Range("myRange").Column

So you could do something like:

myNamedRange = ActiveWorkbook.Sheets("mySheet").Range("myNamedRange").Value
myCol = ActiveWorkbook.Sheets("myMainSheet").Rows("1:1").Find(myNamedRange).Column
ActiveWorkbook.Sheets("myMainSheet").Cells(1, myCol).EntireColumn.Insert