Hi,

I have a workbook with multiple tabs. All tabs have a defined names region.
For example if a tab is named 401 there is a name in the tab for 401 =
A1:P400. I would like to change the region selection all at once so it will
change to 401= A1:Z400. I would like to use a macro to change regions for all
the tabs rather doing this manually one by one. Your help is truly appreciate
it.

Thanks in advance

Nikki
by one.

No need for a macro, this is relatively easy to change.

First, go to Insert - Name - Paste. Choose to "Paste List".

Second, select the cells containing the formulas, and do a find & replace to
change P400 to Z400.

Finally, select the group of cells with names and formulas, and goto Insert
- Name - Create. Define Name labels as left column. Replace old name
definitions.


If this is something that you would need to do often, you could record this
as a macro, I suppose.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Nikki" wrote:

Hi,

I have a workbook with multiple tabs. All tabs have a defined names region.
For example if a tab is named 401 there is a name in the tab for 401 =
A1:P400. I would like to change the region selection all at once so it will
change to 401= A1:Z400. I would like to use a macro to change regions for all
the tabs rather doing this manually one by one. Your help is truly appreciate
it.

Thanks in advance

Nikki
by one.

Nikki,

Try this macro

Sub Resize_Ranges()
For x = 1 To Worksheets.Count
Set Rng = Range(Sheets(x).Name)
Rng.Resize(Rng.Rows.Count, 26).Name = Sheets(x).Name
Next
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Nikki" wrote:

Hi,

I have a workbook with multiple tabs. All tabs have a defined names region.
For example if a tab is named 401 there is a name in the tab for 401 =
A1:P400. I would like to change the region selection all at once so it will
change to 401= A1:Z400. I would like to use a macro to change regions for all
the tabs rather doing this manually one by one. Your help is truly appreciate
it.

Thanks in advance

Nikki
by one.