Rename sheet to filename word before the first space.

[jdoncouse]

I have a list of inventory forms that I keep up with every month. I've been trying to automate a lot of what I do and am close to finishing my macro excel document. The format of all the workbook names is *FirstInitialLastname_Inventory_Year.xls. I have my workbook pull the sheets from these workbooks and rename them to the filename. How do I only rename the sheets to only the FirstInitalLastname part so I can compare the names to a list to see who all I don't have?

The code I currently have is

With ActiveWorkbook    ActiveSheet.Name = Left(.Name, 30)
End With

 

[mumps]

Try:

Sub test()
    ActiveSheet.Name = Left(ActiveSheet.Name, WorksheetFunction.Find("_", ActiveSheet.Name, 1) - 1)
End Sub

 

[jdoncouse]

Sadly that didn't do it. Gives me *unable to get the Find property of the WorksheetFunction class* I'll add more information on what's going on in my code.

Do While Filename <> ""

Workbooks.Open Filename:=Path, ReadOnly:=False
With ActiveWorkbook
    ActiveSheet.Name = Left(.Name, 30)
End With

My master inventory form workbook opens other workbooks *other employees inventory forms* and while they are open changes the names to the corresponding workbooks name *FirstInitialLastname_Inventory_Year.xls* Then adds them to my master form

 

[jdoncouse]

Now that I think about it. I have to have 30 in the code for the name because some of the filenames would extend the 31 mark for the sheet names. Do you think that could be the problem here?

 

[jdoncouse]

Ok figured it out. It was kinda my own fault at explaining. I put "_" as if it was an underscore when I actually ment "space" in the filenames. Thank you though it worked!

 

[mumps]

My pleasure.

 

[Peter_SSs]

FWIW, vba has its own equivalent of Find so I would use that in preference to the worksheet.function. So if you are sure the file name will always have a space in it, you could use

With ActiveWorkbook
  ActiveSheet.Name = Left(Left(.Name, InStr(1, .Name, " ") - 1), 30)
End With

If it might be possible there are no spaces in the workbook name, you could just add one (though that would leave any filename extension in the sheet name) & that middle line of code would become:

ActiveSheet.Name = Left(Left(.Name, InStr(1, .Name & " ", " ") - 1), 30)

Another simple option for that code line that should work with or without spaces would be:

ActiveSheet.Name = Split(.Name)(0)