rename & hide worksheet problem

[BeSmart]

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
_____________________

 

[Mike H]

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

 

[Peter T]

For Each sh In Worksheets

If Left(sh.Name, 20) = "Booking Form Revised" Then
Sheets("Booking Form Revised").Name = "Old BKF chgd " & x

If left$(lcase(sh.name,20) = Lcase("Booking Form Revised") then
sh.name = "Old BKF chgd " & x

Of course this will only work with one such named sheet, you won't be able
to name a second sheet as "Old BKF chgd " & x.

If you expect to rename multiple sheets you will need a bit more. How about

sh.name = Replace(sh.name, "Booking Form Revised", "Old BKF chgd", , ,
vbTextCompare) & x

Regards,
Peter T

 

[BeSmart]

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 Before:=Sheets(3)
Sheets("Booking Form (2)").SelectSheets("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.

 

[BeSmart]

Thanks Peter

Hopefully I won't be using the worksheet name (Old BKF chgd" & x) again
because the "x" converts into todays date and time at the end of the file
name.
e.g. "Old BKF chgd 12-11-08 2258"
so the next time it's used it will apply a different time and possibly date.

The "Booking Form Revised" worksheet is created via a prior macro, not by a
user.
The user clicks a button and the worksheet is created showing revised data
(along with other worksheets).

I'm hoping that by renaming and hiding the previous worksheet called
"Booking Form Revised", that the "MBARevised" macro can then re-use the
worksheet name "Booking Form Revised" next time the user clicks the "create
revised MBA" button however many times.

Pls see my reply to Mike H as my problem still exists...
--
Thank for your help
BeSmart

For Each sh In Worksheets
If Left(sh.Name, 20) = "Booking Form Revised" Then
Sheets("Booking Form Revised").Name = "Old BKF chgd " & x

If left$(lcase(sh.name,20) = Lcase("Booking Form Revised") then
sh.name = "Old BKF chgd " & x

Of course this will only work with one such named sheet, you won't be able
to name a second sheet as "Old BKF chgd " & x.

If you expect to rename multiple sheets you will need a bit more. How about

sh.name = Replace(sh.name, "Booking Form Revised", "Old BKF chgd", , ,
vbTextCompare) & x

Regards,
Peter T

 

[Mike H]

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]

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??

 

[BeSmart]

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.