Identical Named Ranges on Multiple Sheets ... Possible?

[Sandy Radjenovic]

I have a workbook with seven identical worksheets (each holding a schedule for a particular machine). I have a macro that runs and reformats the columns. I would like to name the column ranges so that if a column is added or deleted, I do not have to rename the columns selected in formatcolumns(). Is it possible to do this? Can I have identical named ranges on all of the worksheets (i.e. column A = status, column B= job, etc.)? I have tried this and have noted that if I define a named ranged in worksheet 2 that I had already set in worksheet 1, worksheet 1's named range no longer exists. Is there some way to do this?

I'd really appreciate some insight.


Sub FormatColumns()

Application.ScreenUpdating = False

Range("status").Select
'Columns("A:A").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
.ColumnWidth = 11.5
End With

... commands exist after this code for rest of columns

End Sub

 

[Datsmart]

Hi Sandy,

Yes, Excel allows you to do this.
In the Range Name Define box, preface the range with the WorkSheet name.
IE: =Project_Schedule!$A$1:$L$19
You will only be able to see the Named Range when the WorkSheet that it refers to is the current sheet.
Hope this helps.

John

 

[shades]

I have found NameManager add-in helpful...

http://www.decisionmodels.com/downloads.htm

 

[Sandy Radjenovic]

This works great!
Thanks alot.