Copy paste a particular cell value from all Sheets to Index Sheet

Try:

Sub PasteVal()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Dim foundName As Range
    For Each ws In Sheets
        If ws.Name <> "Index" Then
            Set foundName = Sheets("Index").Range("C:C").Find(ws.Name, LookIn:=xlValues, lookat:=xlWhole)
            If Not foundName Is Nothing Then
                foundName.Offset(0, 1) = ws.Range("N1")
            End If
        End If
    Next ws
    Application.ScreenUpdating = False
End Sub

The sheet name for MMOD24AC has an extra space at the end of the name. Delete that extra space or the macro will not recognize it.