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

 

[Poppaby]

Yes - each workbook has 1 sheet

 

[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(1).Name = Sp(4)
      Wbk.Close True
      Fname = Dir
   Loop
End Sub