Rename Worksheet with Delimited Workbook name

[Poppaby]

Hi All

I have a folder with a number of workbooks. Each file has 1 tab called "sheet 1".

Each workbook has a long complex name in the form of xxxxx_xxx_xxxx_xxxxxx_y_xxxxxxxx_x

where the x is a set length on every workbook, but the y is a random length in each file

I would like to rename "sheet 1" in every workbook in a set folder to "y"

Is there a way to use the "_" delimiter to create a macros for this purpose?

Additionally, is there a way to then expand on this result to further amend the name - like a recurring if function so that

if "y" = Apples then rename the worksheet RED_Apples

Thanks in advance for any help you can provide

Dave

 

[Fluff]

Hi & welcome to MrExcel.
Will the "y" always be the 5th element of the workbook name?

 

[Poppaby]

Thank you - yes Y is always in the same position between delimiter 4 and 5

 

[Fluff]

Ok, how about

Sub Poppaby()
   Dim Pth As String, Fname As String
   Dim Sp As Variant
   Dim Wbk As Workbook
   
   Pth = "C:\MrExcel\Fluff\"
   Fname = Dir(Pth & "*.xls*")
   Do While Fname <> ""
      Set Wbk = Workbooks.Open(Pth & Fname)
      Sp = Split(Fname, "_")
      Wbk.Sheets("Sheet 1").Name = Sp(4)
      Wbk.Close True
      Fname = Dir
   Loop
End Sub

Change the Pth to suit

 

[Poppaby]

Thank you

This seems to change the sheet name to xxxxx_xxx_xxxx_xxxxxx_y
cutting off the name when the character limit is reached.

I need it to be y only if that is possible

 

[Fluff]

Did you copy/paste the code into a module, or re-type it?

 

[Poppaby]

copy paste, and replaced the path name

 

[Fluff]

In that case something funny is going on, as there is no way that code can just take the 1st 31 characters of the filename.
Did you get any error messages?

 

[Poppaby]

I think I see my error - the problem isnt as complicated as I first thought

So the work book name is xxxxx_xxx_xxxx_xxxxxx_y_xxxxxxxx_x

However the first sheet is randomly generated name - I just need a macros that will rename the sheet to Y

Thanks again

 

[Fluff]

Is it the only sheet in the workbook?