I recieve a workbook each day with approx 500 tabs of call data and I would like to relabel all the tabs with the user names. The user names are in a merged range "A7:M7". I'm having difficulty getting the following script to rename the sheets. Currently the values in "A7:M7" look like "User: ADAM ENGEMANN-558".
I am trying to delete each occurrence of "User: " while naming the sheets as well as removing the merged range before doing so that the names are in A7 only. The sheet names should look like "ADAM ENGEMANN-558", or, ideally just "ADAM ENGEMANN". For some reason my code will not modify the cells and I can't figure out why. It's really driving me crazy. Any help much appreciated! (see attached example)
This works if I manually unmerge the cells before running and remove the ":".
Sub RenameSheets2() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Range("A7").Value <> "" Then ws.Name = ws.Range("A7").Value End If Next ws End Sub
This his how I modified it but it doesn't work on the supplied sheets. Please note that if I manually insert some blank sheets into my workbook and merge the cells etc it does work. There seems to be something odd going on with the sheets they are giving me. (they aren't protected)
Sub RenameSheets2() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Range("A7").Value <> "" Then 'I tried adding the following to remove the merged ranges and remove 'User: ' from the names Range("A7:M7").Select With Selection .WrapText = False .MergeCells = False End With Range("A7").Select Selection.Replace What:="User: ", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False On Error Resume Next ws.Name = ws.Range("A7").Value End If Next ws End Sub