1

We have a workbook that needed to have the sheets change names every month and I decided to automate it for other employees. So after some research I found the best way to do it was to reference the names of cells. I needed it to start running on the 4th sheet and run through the second last sheet. I found some VBA code and edited it until I got to this point:

Sub RenameSheet()
Dim ShCnt As Integer      'count of sheets in workbook
  Dim myarray() As String   'array of new worksheet names
  Dim Month() As String     'mystery variable -- not used in this code
  Dim i As Integer          'loop counter
  Dim Lrow As Integer       'number of new worksheet names.
 
  ThisWorkbook.Sheets("SETUP").Select  'select the sheet that has the list of new names
  Lrow = Range("T1").End(xlDown).Row  'get range that contains new worksheet names
  ShCnt = ThisWorkbook.Sheets.Count   'get number of worksheets in the workbook
  ReDim myarray(1 To Lrow)            'resize array to match the number of new worksheet names
   
  For i = 1 To UBound(myarray)   'loop through array of new sheet names
    myarray(i) = Range("T" & i).Value 'insert new sheet name into array
    Debug.Print Range("T" & i).Value    'show the new worksheet name in 'the Immediate window to be able to check that  we're getting what we want
  Next i                                'end of loop
 
  For i = 4 To ShCnt - 1                 'loop through array of existing worksheets
    Sheets(i).Name = myarray(i)       'rename each worksheet with the matching name from myarray
  Next i                              'end of loop
 
  MsgBox "Sheets name has changed successfully"   'report success
End Sub

My issue is that I need the 4th sheet to start with the value in cell "T2". I have figured out that this section of code changed the starting point:

  For i = 1 To UBound(myarray)   
    myarray(i) = Range("T" & i).Value 
    Debug.Print Range("T" & i).Value    
  Next i

When I replaced myarray(i) = Range("T" & i).Value with myarray(i) = Range("T2" & i).Value it started on cell T24 for some reason (which may have to do with the placement of my button?) and myarray(i) = Range("T" + 1 & i).Value doesn't work.

I also tried changing the For i = 1 To UBound(myarray) to For i = 2 To UBound(myarray) and that didn't work either.

Can someone please help me figure out how to get it so that the information in cell T2 ends up on the 4th sheet and goes from there? Thank you very much in advance.

Psycolo
  • 13
  • 3

2 Answers2

0

I would suggest loop through worksheets in the workbook and use the loop counter to index into the range of names in column T:

Sub RenameSheet()

  Dim ShCnt As Integer
  Dim i As Integer
 
  Dim ws_setup As Worksheet
  Set ws_setup = ThisWorkbook.Worksheets("SETUP")
  
  ShCnt = ThisWorkbook.Worksheets.Count
  
  Const start_ws_index = 4
  For i = start_ws_index To ShCnt - 1
    ThisWorkbook.Worksheets(i).Name = _
        ws_setup.Range("t2").Offset(i - start_ws_index, 0).Value
  Next i
  
End Sub
JohnnieL
  • 1,192
  • 1
  • 9
  • 15
  • Thank you very much this works beautifully. It did present me with 1 additional unforseen issue. Is there a way that it will run if a name from the list already exists? As in, it will run and overwrite all of the names regardless of what the sheet names are or the list names are? – Psycolo Feb 15 '21 at 13:46
  • There is no built in way in VBA to check if a Worksheet exists so you have to write a couple of lines of error handling see this https://stackoverflow.com/questions/6688131/test-or-check-if-sheet-exists - you then write an `if` statement to test if a worksheet with the name exists and only rename if it does not. – JohnnieL Feb 15 '21 at 13:49
  • Ok thank you. It was throwing an error saying the sheet name already existed, which in this case won't matter. It just needs to do a straight overwrite of all of the names every time. – Psycolo Feb 15 '21 at 13:52
  • then either side of the line wheer the name is set put `on error resume next` before and `on error goto 0` after. This tells VBA to keep going if an error is found and then resets to standard eror handling – JohnnieL Feb 15 '21 at 13:55
  • That did the trick! Thank you for all of your help! – Psycolo Feb 15 '21 at 13:59
0

Rename Sheets From List

  • In the current setup, it is assumed that the list is contiguous (no blanks), has at least two entries, and starts in cell T2, and that the 4th sheet is the first to be renamed.

The Code

Option Explicit

Sub renameSheets()
    
    ' Constants
    Const wsName As String = "SETUP"
    Const FirstCell As String = "T2"
    Const FirstSheetIndex As Long = 4
    ' Workbook
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    ' Array (at least two names)
    Dim SheetNames As Variant
    With wb.Worksheets(wsName)
        SheetNames = .Range(FirstCell, .Range(FirstCell).End(xlDown))
    End With
    ' Rename
    Dim shOffset As Long: shOffset = FirstSheetIndex - 1
    Dim i As Long
    For i = 1 To UBound(SheetNames, 1)
        wb.Sheets(i + shOffset).Name = SheetNames(i, 1)
    Next i
    ' Inform
    MsgBox "Sheet names changed successfully", vbInformation

End Sub
VBasic2008
  • 44,888
  • 5
  • 17
  • 28