Worked perfectly. Can we accomplish the same thing in VBA?
More questions coming regarding of features with the same spreadsheet.
Bill Prew
Sure in VBA, add this function, and then use it in a formula like:
=LogNumber()
Public Function LogNumber() a = Split(Split(ThisWorkbook.Name, ".")(0), "-") LogNumber = a(1) & "-" & a(2) & "-" & ThisWorkbook.ActiveSheet.NameEnd Function
Well that works for Tab 02 but does not work when you duplicate Tabs 03 and 04.
In addition while the worksheet is named
EE-1711-Rev2, occasionally it could be Rev2A. You can count on the YYMM appearing between the two dashes and that those will be the only two dashes. We can force a 3-character lead like EEE-1711-RevX so that you could count on position 5 to be YY and position 7 to be MM.
Bill Prew
It worked okay for me on other sheets.
So, what are the rules on finding the "EE" and "1711" in the file name, being very specific?
»bp
Bill Golden
ASKER
I misspoke earlier. It is not working in Tab 02 (I forgot to remove the original formula from the cell. I think, but am not sure, that I was supposed to insert the cell number in the VBA code, but did see where to do so and put it in "This worksheet" so that it appears in every tab.
You didn't have to change the VBA at all, nor pass anything to it. Here is a slightly tidier version that makes sure it gets the sheet name of the sheet the formula is on, just to be safe. I also changed the way it gets the data to assume the parts you want are to the immediate left of the REV part, which is assumed to be last. So anything else to the far left doesn't matter. For example these work:
Well, I obviously am doing something in error adding the VBA code. I have attached a copy of the original spreadsheet with Tabs 02, 03 and 04 with the code added. None are working. DTL-EE-1711-Rev4.xlsm
Bill Prew
You needed that VBA code in a new Module, not attached to a sheet.
And then you needed to add the formula to call it on each sheet in the cell you wanted it to appear in.
Open in new window
»bp