VBA to Replace "-" to "_" in excel sheet name

[timleonard]

I am trying the use VBA to create a named range of each Sheet Name. The problem is the Sheet Names contain a hyphen and I guess that can't be used in a named range.

How can I use VBA to rename or replace the existing portion of the sheet name that contains "-" to "_"

[Carruthers Pen]

Try something like this replacing sheet name with variable that gives the sheet name:

dim l as integer
dim x as integer
dim a as string
 
for sheet = sheet 1 to sheet x
a = sheet name
l = len(a)
x = instr(1,a,"-")
if x > 0 then
right((left(a,x),1) = "_"
end if
next sheet

[Stewart Ross]

Hi. There is no problem with using a hyphen in a worksheet name. Although it is not an allowable character in a named range within a workbook, this is where you have named a block within a worksheet (for example, by giving a name to a table used for reference purposes). This is not the same as referring to a range within a worksheet.

For example, if you had two worksheets in a workbook, one named ABC-123 and the other ABC-456, you could place a link to cell A2 of worksheet ABC-123 into worksheet ABC-456 just by using the following formula:

='ABC-123'!A2

Anyhow, if you need to replace occurrences of a substring in a text entry the VBA Replace function will do the job for you:

<new text> = Replace(<old text>, <what you want to replace>, <what you replace it with>)
 
Dim lngCount as Long
For lngCount = 1 to Worksheets.Count
With Worksheets(lngcount)
  .Name = Replace(.Name, "-", "_")
End With 
 

-Stewart

[timleonard]

Thanks so much.......This does the trick