Macro in 2003 works, in 2010 doesnt work (part 2)

[MRichmond]

I have a second macro, created and working perfectly in Excel 2003, but now refuses to work in Excel 2010.

The idea behind the workbook is to keep a whole years audits (done every two weeks) in the same file (instead of 26 workbooks).

So on opening the user can either choose to view an existing audit, and in which case they are shown a summary sheet (hiding the home page), with hyperlinks to the audits they can select, and a graph showing their score progression.

If they click on new, it creates a copy of the hidden template question sheet (again hiding the home page), and once a date it added in the relevant cell, the sheet is renamed to the date.


Thats how it worked in Excel 2003.


However in Excel 2010, it appears to rename the home (Landing) page, doesnt hide it, and although it creates a copy of the template this remains hidden.

Any chance some can take a look and see what I need to change to have it work in both Excel 2003 & Excel 2010 (it needs to work in both as not all users have been upgraded to the later version.

I'm attaching a copy of the workbook with the code to help.

[Bob Phillips]

Your code is protected.

[MRichmond]

Sorry forgot it was password protected11298

[Bob Phillips]

Private Sub cmdNew_Click()
Dim Sh As Worksheet, TemplateSh As Worksheet
Dim ShNum As Long, HighestNum As Long
Dim SheetCoreName As String

Application.EnableEvents = False
Application.ScreenUpdating = False

SheetCoreName = "Sheet"

Set TemplateSh = Worksheets("Template")

' DETERMINE NEXT NUMBER FOR SHEET
For Each Sh In Worksheets
If InStr(1, Sh.Name, SheetCoreName) = 1 Then
ShNum = Val(Right(Sh.Name, Len(Sh.Name) - Len(SheetCoreName)))
If ShNum > HighestNum Then HighestNum = ShNum
End If
Next Sh

TemplateSh.Visible = xlSheetVisible
TemplateSh.Copy after:=Worksheets(Worksheets.Count)
Set Sh = ActiveSheet
Sh.Visible = xlSheetVisible
Sh.Name = SheetCoreName & HighestNum + 1
TemplateSh.Visible = xlSheetHidden

Unload Me

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

[MRichmond]

XLD, thanks very much, you are the man



:beerchug: