Help! (please)

I have a workbook that is a Field Report. It starts with one visible sheet and when the user changes a certain cell to a particular piece of text a "new" sheet is "created" - in reality it is simply an existing sheet which is unhidden by the code. This "new" sheet is called "OEM". The workbook has lots of VBA code for Workbook_BeforeSave which checks that some mandatory cells have been completed on OEM and other sheets. All is working well. But . . . It may be that the user wants to create another OEM sheet. I've achieved this by copying the orinal OEM sheet, so we have OEM and OEM(1). All good so far, except of course the VBA code does not work for the new sheet "OEM(1)"

I hope that's explained clearly enough. So my question is, what is the best way to get around this i.e. get some VBA code to run on OEM(1)? I realise I could create more code for potential new sheets but am hoping there is a more elegant way?

Any help gratefully received.

Steve

Hi Steve,

Am Wed, 9 Dec 2015 06:58:52 -0800 (PST) schrieb :

I have a workbook that is a Field Report. It starts with one visible sheet and when the user changes a certain cell to a particular piece of text a "new" sheet is "created" - in reality it is simply an existing sheet which is unhidden by the code. This "new" sheet is called "OEM". The workbook has lots of VBA code for Workbook_BeforeSave which checks that some mandatory cells have been completed on OEM and other sheets. All is working well. But . . . It may be that the user wants to create another OEM sheet. I've achieved this by copying the orinal OEM sheet, so we have OEM and OEM(1). All good so far, except of course the VBA code does not work for the new sheet "OEM(1)"

I hope that's explained clearly enough. So my question is, what is the best way to get around this i.e. get some VBA code to run on OEM(1)? I realise I could create more code for potential new sheets but am hoping there is a more elegant way?

try in Workbook_BeforeSave

For Each wsh In Worksheets
If Left(wsh.Name, 3) = "OEM" Then
your code


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Superb! Thanks again Claus. I've adapted it a bit and it's doing exactly what I want. I was stumped for a long time until I realised the line Cancel = True was cancelling things and not allowing it to go on and do the save.

Cheers!

Steve