Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can multiple spreadsheets be linked to change simultaneously? | Excel Discussion (Misc queries) | |||
have to change all the comments in the sheet simultaneously | Excel Discussion (Misc queries) | |||
Auto change defined name ranges on Jan 1st annually | Excel Worksheet Functions | |||
defined name ranges | Excel Programming | |||
Simultaneously change values in multiple cells? | Excel Worksheet Functions |