I worked it out...
I moved the renaming actions into the very end of the macro that is run
directly before this one (via a different button).
The Booking Form Revised sheet is renamed and hidden.

Now when the RevisedMBA code runs, the worksheet has already been renamed
and I just unhide it - without conflicting with any other worksheet names.

--
Thank for your help
BeSmart


"BeSmart" wrote:

Hi Mike

It looks like both macros are being checked for errors before they run them
and the check is finding the conflict with the sheet names (in the second
macro) before it runs the first macro. But if the first macro runs - it
which will solve the problem? Or is that not possible???

Yes - I thought about adding a variable at the end of the sheetname.

However my code references the latest "Booking Form Revised" worksheet later
when it's running.

I don't know how to write the code so the macro picks the latest "Booking
Form Revised" sheetname with the variable at the end of the name??

--
Thank for your help
BeSmart


"Mike H" wrote:

Hi,

You found 2 sheets the met your criteria and tried to give them both the
same sheetname which isn't allowed. Every time you do your hide increment a
variable (say) y by 1 and add this to your sheetname with

sh.Name = "Old BKF chgd " & x & y
y=y+1


Mike


Mike

"BeSmart" wrote:

Hi Mike H
My apologises for being a novice and not explaining my problem better.
and thanks for your revised code - it worked fantastic in a test workbook.

But when I put it back into my live workbook and it "called RevisedMBA" the
following error message appeared near the beginning of the second macro -
when it tried to rename a the worksheet called "Booking Form" to be named
"Booking Form Revised"

"Run-time error '1004':
Cannot rename a sheet to the same name as another sheet, a reference object
library or a workbook referenced by Visual Basic

When I hit debug the error occurs in the below position:

....
Sheets("Booking Form").Select
Sheets("Booking Form").Copy Befo=Sheets(3)
Sheets("Booking Form (2)").Select
Sheets("Booking Form (2)").Name = "Booking Form Revised" <<<error
Sheets("Booking Form Revised").Activate
....

I want to rename and hide the previous worksheet called "Booking Form
Revised" so that the "MBARevised" macro can run and re-use that worksheet
name each time the user wants to create revised worksheets.

--
Thank for your help
BeSmart


"Mike H" wrote:

Hi

Not working isn't a very helpful description of the problem but looking at
the code you are testing the leftmost 20 characters and then when you find
that sheet you are making an assumption there are only 20 characters in the
sheet name. Try this
Sub Checksheets()
Dim Flag As Integer
x = WorksheetFunction.Text(Now(), "dd-mm-yy at hhmm")
Flag = 0

For Each sh In Worksheets
If Left(sh.Name, 20) = "Booking Form Revised" Then

sh.Name = "Old BKF chgd " & x

Flag = Flag + 1
sh.Visible = xlSheetHidden
End If
Next

If Flag = 0 Then
MsgBox Flag
'Call RevisedMBA
End If
End Sub


Mike
"BeSmart" wrote:

Hi All

I can't work out why my code is not working here?
I need to find a worksheet, rename and hide it before I can run the next
code...

The code needs to:

- Find any worksheet names starting from the left with the following 20
characters:
"booking form revised"
(any hidden sheets can be ignored)

- If it doesn't find any worksheets with this name then skip the rest of
the macro and call "RevisedMBA"

- If it does find a worksheet - Rename that worksheet to: "Old BKF chgd " & x
(x = WorksheetFunction.Text(Now(), "dd-mm-yy at hhmm")
I know that I'm limited to 31 characters in a worksheet name - I've kept
under that - just with 29.

- Hide the renamed "Old BKF chgd " & x worksheet

- Now call RevisedMBA.

Current Code:
_________________________________
Sub Checksheets()

Dim Flag As Integer
x = WorksheetFunction.Text(Now(), "dd-mm-yy at hhmm")
Flag = 0

For Each sh In Worksheets
If Left(sh.Name, 20) = "Booking Form Revised" Then
Sheets("Booking Form Revised").Name = "Old BKF chgd " & x
Flag = Flag + 1
sh.Visible = xlSheetHidden
End If
Next

If Flag = 0 Then
Call RevisedMBA
End If
End Sub
_____________________
--
Thank for your help
BeSmart