Transpose cell range into sheet names

[nickharg]

Does anyone know if it's possible to transpose a vertical range of cells in, say, Sheet1, so that the names of new worksheets are automatically changed?

For example: Have the names of Sheets 2-6 automatically change to equal the values in range A1:A5 in Sheet1

Thanks

 

[nickharg]

Right on, it works when I swap out the sheet name. I was hoping this would allow the sheet names to automatically refresh (like an =transpose function) when any cell in A3:A52 is altered, but this is much better than having to put them all in manually in the first place.

Thanks for your help

 

[Rick Rothstein]

Right on, it works when I swap out the sheet name. I was hoping this would allow the sheet names to automatically refresh (like an =transpose function) when any cell in A3:A52 is altered, but this is much better than having to put them all in manually in the first place.

You can use event code (different from a macro) to do that. Right click the name tab on the worksheet containing the A3:A52 range where the names will be typed, select View Code from the popup menu that appears and copy/paste this code into the sheet module code window that opened up...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  If Not Intersect(Target, Range("A3:A52")) Is Nothing Then
    For Each Cell In Intersect(Target, Range("A3:A52"))
      If Len(Trim(Cell.Value)) Then
        Worksheets(3 + Cell.Row).Name = Cell.Value
      Else
        Application.Undo
      End If
    Next
  End If
End Sub

Once you have done that, typing anything into cells A3:A52 will instantly rename the associated worksheet with the text in that cell. Note (and I guess this applies to the macro I gave you earlier if, as it now seems, you would run it more than once) this code will be affected if you rearrange the order of any of the worksheets... the code basically counts over from the sixth worksheet the same number of sheets that the changed cell's row is down from A3. In other words, the names in the sheet tabs do not figure into how the code works... it simply renames whatever worksheet is located in the numerical offset from the start sheet (6th tab) no matter how that sheet got into that position. So, if you or your user drags sheets into new locations, the odds are typing a value in the range A3:A52 will end up renaming a different sheet than you might originally be intending.

 

[nickharg]

Thanks a ton, works perfectly. I'll keep your disclaimer in mind. I actually noticed that issue when I inserted an "Instructions" sheet before my 'Invoiced' (my initial Sheet1) but was easily able to alter your code by changing "Worksheets(X + 3).Name = Worksheets("Sheet1").Range("A" & X).Value" to Worksheets(X + 4).Name = Worksheets("Sheet1").Range("A" & X).Value"

I'll make it clear in the instructions for the user not to alter the order of all the sheets.

Thanks again.

 

[Rick Rothstein]

Thanks a ton, works perfectly. I'll keep your disclaimer in mind. I actually noticed that issue when I inserted an "Instructions" sheet before my 'Invoiced' (my initial Sheet1) but was easily able to alter your code by changing "Worksheets(X + 3).Name = Worksheets("Sheet1").Range("A" & X).Value" to Worksheets(X + 4).Name = Worksheets("Sheet1").Range("A" & X).Value"

I'll make it clear in the instructions for the user not to alter the order of all the sheets.

Thanks again.

You are welcome. Just one other note (I wouldn't think it should actually affect you)... you cannot delete an entry in cells A3:A52 as that would attempt to name a sheet with the empty string which is not allowed, so I have the code Undo the action if attempted.

 

[nickharg]

Duly noted. Thanks for everything.