0

Would really appreciate any help that can be provided! I feel like this question is basic and should be easy to figure out but I am struggling. I have an Excel 2016 workbook that, if a set-up error is made, code will need to run to reset the names of the worksheets back to a "standard". The worksheet location (Sheets(1)) can't be used as the worksheet position may change and the visible worksheet name (Sheets("Name")) can't be used as it won't be constant. This leaves using the codename (Sheet1) as the appropriate option. I set-up an array and simple code to rename the sheets but it won't run.

'WSName to define array of sheets by codename
'NewName to create reference to cell which contains new worksheet name

Dim WSName as Variant
Dim NewName as Variant
WSName = Array ("Sheet4","Sheet15","Sheet17","Sheet18")
NewName = Array("D15","D16","D17","D18")

'counter to cycle through arrays and rename the 4 worksheets

For Ncounter = 0 To 3
Sheets(WSName(Ncounter)).Name=Range(NewName(Ncounter)).Value
Next Ncounter

I can replace the WSName array with the user added sheet names and the code works. I've also tried removing "Sheets" from the code as I'm effectively saying SheetsSheetX but still nothing.

I think I am missing the proper syntax for referencing a sheet by codename. Any help or suggestions would be appreciated and I apologize for what, I think, is a newbie and just don't get it question.

Update: Based upon Tim's answer, I did this:

'WSName to define array of sheets by codename
'NewName to create reference to cell which contains new worksheet name

Dim WSName, NewName as Variant

WSName = Array (Sheet4, Sheet15, Sheet17, Sheet18)
NewName = Array("D15","D16","D17","D18")

'counter to cycle through arrays and rename the 4 worksheets

For Ncounter = 0 To 3
WSName(Ncounter).Name=Range(NewName(Ncounter)).Value
Next Ncounter

This code cycles through 4 sheets out of 20 and renames those 4 to the values listed on the active worksheet. For the Dim, I'm sure that long instead of variant will work fine (better actually), just need to research that one a little more before switching out so I'm sure I'm comfortable with the difference between the 2. Thanks again to all that responded...... Angelic

Community
  • 1
  • 1
jonesab13
  • 3
  • 6
  • It is basic, you're right. Just loop through the sheets and set each one's Name to its Codename: `Sheets(Ncounter).Name=Sheets(Ncounter).CodeName`. – Doug Glancy Feb 18 '16 at 18:07
  • "but it won't run." , meaning what exactly ? error message? and `=Range(NewName(Ncounter)).Value`might need a reference to a sheet, or activesheet needs to be the good one. – Patrick Lepelletier Feb 18 '16 at 23:37
  • also the sheets to change must have the exact same name as in WSName before you run this. After you runned it, next time those sheets names won't exist anymore and give an error, so might need error handling. – Patrick Lepelletier Feb 18 '16 at 23:42
  • 1
    Thanks Doug and Patrick! I like the simplicity of Doug's response but, unfortunately, that wouldn't work in this instance as I need to rename 4 out of ~20 sheets back to the "original" name which, in this case is "Coating1, Coating2,etc". I'll watch out for the same name issue that Patrick mentioned but, don't believe it should be a problem as this code will only run in 1 file which is a template used for generating formatted SOPs. Still something to be aware of as end users can do wacky things. – jonesab13 Feb 19 '16 at 13:53
  • I see. if it was me I'd set the codenames in advance and just refer to those. Glad you got it working. – Doug Glancy Feb 19 '16 at 14:44

1 Answers1

1

You can put the sheet directly into an array - don't need to use the codeNames:

Dim arrWS, NewName, Ncounter As Long
arrWS = Array (Sheet4, Sheet15, Sheet17, Sheet18)
NewName = Array("D15","D16","D17","D18")

'counter to cycle through arrays and rename the 4 worksheets

For Ncounter = 0 To 3
    arrWS(Ncounter).Name = Range(NewName(Ncounter)).Value
Next Ncounter

You need to specify which worksheet holds the sheet names you want to reset to. just using "Range()" will default to the ActiveSheet.

That's assuming the sheets being renamed are in the same workbook as the code above. If that's not the case then go back to using the names and use JFC's function from the link below to get a reference to each of the sheets.

Fully reference a worksheet by codename

In the loop:

GetSheetWithCodename(arrCodeName(Ncounter)).Name = _
                      Range(NewName(Ncounter)).Value
Community
  • 1
  • 1
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • I think the OP just wants to reset the name to the codename, in which case my comment should do it. – Doug Glancy Feb 18 '16 at 18:40
  • I think they want to set the worksheet with codename "Sheet4" to have a tab name equivalent to the value in Range "D15" etc... – Tim Williams Feb 18 '16 at 18:51
  • Thanks Tim! you are correct with the need to rename the sheet based on a value. Key to your answer was removing the quotes from the Array for my WSName (your arrWS) and then removing "Sheets" from within the Ncounter code. Everything is working beautifully now and I was able to add in the other bells needed. Now I need to go research Dim as Long vs Variant could be the key to another sheet I hacked-up as I'm learning VBA! ;) – jonesab13 Feb 19 '16 at 14:04