any way to rename a sheet in VBA?

[dnickelson]

I'm looking to run some queries in VBA and would like the results to come back on a sheet named with a specific part of the query (store number or such). I've searched through the message board but searching for sheet name either returns around 1500 or no matches. Anyone know of a simple example to change a sheet name in VBA. Creating a sheet would work also, may even be better. Not too worried about the format, the simpler the better, I should be able to slip it in where needed.

thanks in advance

Dan

 

[hippo88]

Hi, I have 70 sheets (tabs) with names like 5485-xxxx-F or 4455-00-F. How do I write vba to remove the -xxxx-F or the -F, in other words just rename sheet to the numbers only with -01 or 02 etc ok. thanks

 

[My Aswer Is This]

Assuming we will be doing all sheets in your Workbook.
Try this:

Modify to your needs:

Sub Name_My_Sheets()
'Modified 4/7/2019 5:53:29 PM  EDT
Application.ScreenUpdating = False
On Error GoTo M
Dim i As Long
    For i = 1 To Sheets.Count
        Sheets(i).Name = "0" & i
    Next
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "That sheet name allready exist"
Application.ScreenUpdating = True

End Sub

 

[hippo88]

@My answer - what I meant was to rename sheet (or delete) the part after the numbers. For example if sheet name is 5485-xxxx-F, I would like vba to rename sheet to 5485. And 4455-00-F to rename to 4455-00.

 

[shg]

Sheets(i).Name = left(Sheets(i).Name, len(Sheets(i).Name) - 2)

 

[My Aswer Is This]

Yes I provided a answer I was not sure if would work for you or not. Was not sure if you cared about the exact name or not.

@My answer - what I meant was to rename sheet (or delete) the part after the numbers. For example if sheet name is 5485-xxxx-F, I would like vba to rename sheet to 5485. And 4455-00-F to rename to 4455-00.

 

[My Aswer Is This]

I tried Shg script and it looks to me as if his script does not do what you wanted either.

I would think we would need some sort of replace this for this.
But we will see.

 

[hippo88]

yes shg script removed 2 characters. It's a start. Thank you both very much.

 

[Trebor76]

Though in my humble opinion having a separate tab for each part is not the way to go, try this:

Option Explicit
Sub Macro1()

    Dim wsMySheet As Worksheet

    Application.ScreenUpdating = False
    
    For Each wsMySheet In ThisWorkbook.Sheets
        On Error Resume Next 'Ignore error message if there's no dash in the tab name
            wsMySheet.Name = Evaluate("LEFT(""" & wsMySheet.Name & """,SEARCH(""-"",""" & wsMySheet.Name & """)-1)")
        On Error GoTo 0
    Next wsMySheet
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[My Aswer Is This]

Yes Shg script did do a part but doing the rest will be a challenge.

yes shg script removed 2 characters. It's a start. Thank you both very much.

 

[footoo]

Dim sh%, ray, i%, s$
For sh = 1 To Sheets.Count
    ray = Split(Sheets(sh).Name, "-")
    For i = LBound(ray) To UBound(ray)
        If IsNumeric(ray(i)) Then s = s & ray(i) & "-"
    Next
    If s <> "" Then Sheets(sh).Name = Left(s, Len(s) - 1)
    s = ""
Next