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

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


In article <(E-Mail Removed)>,
(E-Mail Removed) wrote:

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

On Mar 26, 10:42 am, JE McGimpsey <jemcgimp...@mvps.org> 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").Name = "SWP"
> End Sub
>
> In article <1174917514.507298.310...@o5g2000hsb.googlegroups.com>,
>
>
>
> Marlene.Sawh...@gmail.com wrote:
> > 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!!- 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!

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
_______


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Mar 26, 10:42 am, JE McGimpsey <jemcgimp...@mvps.org> 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").Name = "SWP"
>> End Sub
>>
>> In article <1174917514.507298.310...@o5g2000hsb.googlegroups.com>,
>>
>>
>>
>> Marlene.Sawh...@gmail.com wrote:
>> > 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!!- 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!
>