VBA rename sheet based on a string in a cell reference

[jrg24]

I have code that pulls one particular sheet from all workbooks in a directory. Once all the sheets are in, I have them renaming based on the value of a cell. Unfortunately, my preferred name for the sheet does not exist anywhere on the sheet except as a string inside a cell reference. (for example, Cell C6 in my worksheet contains the formula "=+'A:\New Quarterly Process\Q2 2018\Sent Templates\[BR-RIO-2Q2018 Reforecast Template.xlsm]Overheads'!N74". I want to rename my sheet BR-RIO. How would I go about doing that?

thanks for any help you might be able to provide.

 

[Norie]

You could use something like this to extract the required string from the formula.

    strShName = Mid(Range("C6").Formula, InStr(Range("C6").Formula, "[") + 1)

    strShName = Split(strShName, "-")(0) & "-" & Split(strShName, "-")(1)

 

[jrg24]

You could use something like this to extract the required string from the formula.

    strShName = Mid(Range("C6").Formula, InStr(Range("C6").Formula, "[") + 1)

    strShName = Split(strShName, "-")(0) & "-" & Split(strShName, "-")(1)

Thanks, Norie. This does exactly what I needed to do.