Remove last x characters from sheet name

[Gman]

I am sure this is really easy but I have not been able to find anywhere. I need a VBA to remove "_Summary" from every sheet name. All sheets follow this format "location name_Summary"

Thank you for any help

 

[p45cal]

Try this:

Sub blah()
For Each sht In ActiveWorkbook.Sheets
  If Right(sht.Name, 8) = "_Summary" Then sht.Name = Left(sht.Name, Len(sht.Name) - 8)
Next sht
End Sub

 

[chirayu]

@p45cal I've modified your code to provide a non case sensitive version

Sub blah()
For Each sht In ActiveWorkbook.Sheets
  If UCase(Right(sht.Name, 8)) = "_SUMMARY" Then sht.Name = Left(sht.Name, Len(sht.Name) - 8)
Next sht
End Sub

 

[Asheesh]

How about using Option compare text.. It is a question @chirayu

 

[chirayu]

How about using Option compare text.. It is a question @chirayu

Yeah can use that but it'll make any & all comparisons non case sensitive within that module.

By using UCASE you only desensitise specific bits of code so you can have multiple arguments which can check case sensitivity as well if required within the same module.

At the end of the day it depends on what the requirement is. Found a neat Chandoo link below for different ways to compare text (read comments too).

http://chandoo.org/wp/2011/12/01/case-in-sensitive-string-compare-vba/

 

[Gman]

Why wont this code work when I change
If Right(sht.Name, 9) = "_Comments" Then sht.Name = Left(sht.Name, Len(sht.Name) - 9)
Next sht

 

[p45cal]

At first glance it should work.
It may screw up if there's a sheet called _Comments (no prefix).
In what way doesn't the code work? Is there an error message?
Does it never work, or only fail on some sheets? What's the name of the sht when it fails? Is sht a sheet object?

 

[Gman]

it doesn't do anything when I push run? Not very good with VBA what do you mean by sht a sheet object?

 

[p45cal]

By itself that code will do nothing except produce an error. We'd need to see the context (the rest of the macro).
What do you mean by 'push run'?

Of course, if there is no sheet name ending with exactly "_Comments" with no trailing spaces, upper/lower case exactly the same, absolutely nothing will happen.

 

[Gman]

here is the whole code

Original:
Sub blah()
For Each sht In ActiveWorkbook.Sheets
If UCase(Right(sht.Name, 8)) = "_SUMMARY" Then sht.Name = Left(sht.Name, Len(sht.Name) - 8)
Next sht
End Sub

Changed (highlighted in Red):
Sub blah()
For Each sht In ActiveWorkbook.Sheets
If UCase(Right(sht.Name, 9)) = "_Comments" Then sht.Name = Left(sht.Name, Len(sht.Name) - 9)
Next sht
End Sub

The first one works but when I change it to "_Comments" it doesn't. An example of the sheet name is "Tracy_Comments" I want to only have Tracy

 

[p45cal]

UCase(Right(sht.Name, 9)) = "_Comments"
will never be true.
Try:
UCase(Right(sht.Name, 9)) = "_COMMENTS"

 

[Gman]

really, it's all because of the case? Crazy. Thank you

 

[Marc L]

Hi !

I have not been able to find anywhere.

Many samples all over the Web ‼

I need a VBA to remove "_Summary" from every sheet name

sheet name is "Tracy_Comments" I want to only have Tracy

If there is only one underscore in name :​

Sub Demo()
         Dim Ws As Worksheet
    For Each Ws In Worksheets
             Ws.Name = Split(Ws.Name, "_")(0)
    Next
End Sub

Do you like it ? So thanks to click on bottom right Like !
​