Change defined name ranges simultaneously
 

Hello....

I have a defined name range "SWP" that currently refers to cells
A22:D34. 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!!

Change defined name ranges simultaneously
 

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").N ame = "SWP"
End Sub


In article . com,
wrote:

Hello....

I have a defined name range "SWP" that currently refers to cells
A22:D34. 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!!

Change defined name ranges simultaneously
 

On Mar 26, 10:42 am, JE McGimpsey wrote:
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").N ame = "SWP"
End Sub

In article . com,



wrote:
Hello....

I have a defined name range "SWP" that currently refers to cells
A22:D34. 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!!- Hide quoted text -

- 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!

Change defined name ranges simultaneously
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


wrote in message
ups.com...
On Mar 26, 10:42 am, JE McGimpsey wrote:
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").N ame = "SWP"
End Sub

In article . com,



wrote:
Hello....

I have a defined name range "SWP" that currently refers to cells
A22:D34. 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!!- Hide quoted text -

- 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!