Solved: Worksheet naming based on cell data

[cdbrown]

Hi all, stumbled across this site when searching for help on some other code (which was very helpful by the way) and figured someone would be able to point me in the right direction.

Using Excel 2003 SP2 on XP system.

I have a macro that prompts the user to select a folder which contains multiple single sheet workbooks, it then opens each workbook and copies the worksheet into the main workbook, then closes the other one. During the process it renames the worksheet based on the information in 2 cells. This works fine however I've applied this to another data set which contained extra information in the cell which I don't want.

[vba]
With Application.FileSearch
.NewSearch
.LookIn = MyFolder
.FileType = msoFileTypeExcelWorkbooks
.Execute
For i = 1 To .FoundFiles.Count
Set wb2 = Workbooks.Open(.FoundFiles(i))
Sheets(1).Copy After:=Workbooks(wb1.Name).Sheets(z)
If ActiveSheet.Name = "DYNAMIC RESULTS" Then
Size = Cells(10, 1)
Parts = Cells(22, 2)
ActiveSheet.Name = Size & "-" & Parts & "ppm"
End If
If ActiveSheet.Name = "DETAILED DISPERSION REPORT" Then
Size = Cells(9, 1)
ActiveSheet.Name = Size
End If
Application.CutCopyMode = False
Windows(wb2.Name).Close
z = z + 1
Next i
End With
[/vba]
Size returns different information based on the worksheet but they all are very similar 2mm Leak, 7mm Leak, 22mm Leak, 70mm Leak and 150mm Leak.

I would like to remove the " Leak" from Size so that it only returns 2mm, 7mm, 22mm, 70mm and 150mm.

I tried to use
[vba]Size=Application.Left(Cells(10,1),Find("mm",Cells(10,1),1)+1)[/vba] which returns a "Sub or Function not Defined". Is this heading in the correct direction as I know that WorksheetFunction.Left is not applicable as well.

Any advice owuld be greatly appreciated.
Cheers
Craig

[mdmackillop]

Hi Craig,
Welcome to VBAX
Regards
MD

try
[VBA]
ActiveSheet.Name = Right(Size,len(Size)-5)

[/VBA]

[cdbrown]

Thanks for that MD. Tried your code but it resulted in "eak" and "Leak", so I modified it to
[vba]ActiveSheet.Name = Left(Size, Len(Size) - 5)[/vba] and that works great, so thanks for the help.

[mdmackillop]

Just checking that my shoes are on the right feet!