Cannot figure out why this is crashing

[ERLoft]

Seems pretty straightforward and has worked in the past, but now I'm getting an error when attempting to activate the 'Stats' worksheet.

Here's the pertinent part of the code with the failure commented:

Set MTReportwb = ActiveWorkbook
MTReport = ActiveWorkbook.Name

'Grab and move OCC Snapshot data
Sheets("OCC Snapshot").Activate
Cells.Copy

Workbooks(YSName).Activate
Sheets("Data 2").Activate
If TodayDate <> FDNM Then
    Range("A1").PasteSpecial xlPasteValues
End If

'Grab and move current month data
Workbooks(MTReport).Activate
[COLOR=rgb(0, 0, 0)]Sheets("Stats").Activate 'Failure debugs to here...[/COLOR]
Range("A5:T35").Copy

Workbooks(YSName).Activate
Sheets("Data 1").Activate
Range("A32").PasteSpecial xlPasteValues

'Close Multi-Tool report
Application.CutCopyMode = False
MTReportwb.Close Savechanges:=False

Sheets("Data Merge Current").Activate

The sheet is present in the active workbook - the code had just pulled data from the OCC Snapshot sheet right before this error.

 

[DanteAmor]

Check the name of the sheet, that there are no spaces before or after the name.

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[ERLoft]

Definitely no spaces. The same code ran fine yesterday. No changes to any of this part of the workbook since then - only some minor conditional formatting in completely different sheets.

I tested with a sub that has only Sheets("Stats").Activate and it works fine.

 

[ERLoft]

And it gets weirder. I tried to replace the "Stats" reference with a simple Sheet(1) reference and got the same error. Even running a simple snippet to return the sheetindex for that sheet causes the crash. I guess somehow the source file is corrupt?

Funny that it won't reference any sheet with the word 'Stats' in it. I can return the sheet index for every other sheet on the data source sheet except 'Stats' and 'Stats Import'. It's like Excel decided it doesn't like the word Stats.

 

[DanteAmor]

Workbooks(MTReport).Activate
Sheets("Stats").Activate 'Failure debugs to here...

I can't see in your image if it corresponds to the MTReport book.
Could you confirm that the "Stats" sheet is in the MTReport workbook.

Just for checking:
Change the sheet name to "st" and modify the macro to:

'Grab and move current month data
Workbooks(MTReport).Activate
Sheets("st").Activate 
Range("A5:T35").Copy

 

[ERLoft]

Yeah, after re-creating the source data file, everything works as normal. Now, can anyone answer why the source file would suddenly not have valid sheets with 'Stats' in the name?

 

[DanteAmor]

Yeah, after re-creating the source data file, everything works as normal. Now, can anyone answer why the source file would suddenly not have valid sheets with 'Stats' in the name?

I don't know, I have no problem using the sheet named "Stats". Maybe as you mentioned, the sheet or workbook is corrupted.

I guess somehow the source file is corrupt?

I know that there are reserved words that you can't use as a sheet name, but in this case I have no problem with that word.

 

[ERLoft]

Yeah, really weird. I have Master sheets for this source data and save a copy for each month. Only 2 days into the month and apparently that file was somehow corrupt? As mentioned above,
I couldn't even get this to work on the 5-2023 workbook with the Stats sheet active - got the same error message as in my original post:

Sub TestIndex

Dim SheetI As Integer

SheetI = ActiveSheet.Index

MsgBox(SheetI)

End Sub

 

[DanteAmor]

It usually happens when the sheet is corrupted. You have to copy everything to a new book.