Macro error- Expected variable or procedure, not module

[Marlowwe]

Hello,

I found this usefull macro and mz VBA Editor gives me error: Expected variable or procedure, not module.

and Highlight Sheets("Index").Delete

Sub CreateIndex()
'updateby Extendoffice 20150914
    Dim xAlerts As Boolean
    Dim I  As Long
    Dim xShtIndex As Worksheet
    Dim xSht As Variant
    xAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Index").Delete
    On Error GoTo 0
    Set xShtIndex = Sheets.Add(Sheets(1))
    xShtIndex.name = "Index"
    I = 1
    Cells(1, 1).Value = "INDEX"
    For Each xSht In ThisWorkbook.Sheets
        If xSht.name <> "Index" Then
            I = I + 1
            xShtIndex.Hyperlinks.Add Cells(I, 1), "", "'" & xSht.name & "'!A1", , xSht.name
        End If
    Next
    Application.DisplayAlerts = xAlerts
End Sub

or here: Sheets.Add before:=Sheets(1)

Sub CreateIndex2()
Dim sheetnum As Integer
Sheets.Add before:=Sheets(1)
For sheetnum = 2 To Worksheets.Count
 ActiveSheet.Hyperlinks.Add _
 Anchor:=Cells(sheetnum - 1, 1), _
 Address:="", _
 SubAddress:="'" & Worksheets(sheetnum).name & "'!A1", _
 TextToDisplay:=Worksheets(sheetnum).name
Next sheetnum
ActiveWindow.DisplayGridlines = False
End Sub

Where is the problem ? Thank you

 

[gallen]

Does this help?

Looks like a naming conflict?

 

[Marlowwe]

Does this help?

Looks like a naming conflict?

NO.

I rename macro several times and I get the same error.

But when I Run the macro from Other file (.XLSM) then my PERSONAL.XLSB file the macro run normaly without error.

I have a lots of macros (200) in my PERSONAL.XLSB for creating or testing, so Somewhere have to be a thing which gives me the error ?

 

[Fluff]

It sounds like you have a module called sheets in your Personal.xlsb file.
Change the name of the module & try again.
You should never use VBA keywords for Modules, subs, or variables as it can cause problems like this.

 

[Marlowwe]

It sounds like you have a module called sheets in your Personal.xlsb file.
Change the name of the module & try again.
You should never use VBA keywords for Modules, subs, or variables as it can cause problems like this.

WOW, thanks. IT Works.

I had a Module named Sheets_Tested. I renamed and it is Ok,

Thanks Fluff

 

[Fluff]

Glad we could help & thanks for the feedback