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 protectedAudit 2014 (Template) v5.xlsm

[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