Change defined name ranges simultaneously

[Marlene.Sawhney]

Hello....

I have a defined name range "SWP" that currently refers to cells
A2234. However, I need it to update to read A27:F39. This name
range is used in about 75 spreadsheets, however it looks like I have
to change SWP manually for each spreadsheet. Does anyone have a macro
to help change this range or an easy way to update this?

Thank you!!

 

[JE McGimpsey]

If, by spreadsheets, you mean 75 worksheets in a single workbook, with
SWP defined as a sheet-level name, then one way:

Public Sub ChangeSWP()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Range("A27:F39").Name = ws.Name & "!SWP"
Next ws
End Sub

If instead you mean you have 75 workbooks with SWP defined as a workbook
level name, then one way:

Public Sub ChangeSWP()
ActiveWorkbook.Sheets("Sheet1").Range("A27:F39").Name = "SWP"
End Sub

 

[Marlene.Sawhney]

If, by spreadsheets, you mean 75 worksheets in a single workbook, with
SWP defined as a sheet-level name, then one way:

Public Sub ChangeSWP()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Range("A27:F39").Name = ws.Name & "!SWP"
Next ws
End Sub

If instead you mean you have 75 workbooks with SWP defined as a workbook
level name, then one way:

Public Sub ChangeSWP()
ActiveWorkbook.Sheets("Sheet1").Range("A27:F39").Name = "SWP"
End Sub





- Show quoted text -

Thank you! I did mean worksheets. I tried to run the macro and I
have an error that says : "Runtime error 1004: This name is not
valid." However, the name of the defined range is SWP, so I'm not
sure what name its actually calling invalid.

Any ideas ? THANKS!

 

[Jon Peltier]

If the worksheet contains a space or other special character, it must be
enclosed in single quotes:

ws.Range("A27:F39").Name = "'" & ws.Name & "'!SWP"

Use this line anyway. If you don't need the single quotes, Excel removes
them without complaining.

- Jon