June 24, 2016
Have a workbook that has worked for several years. Latest update to Office 365 (apps on laptop - not using on-line versions) has broken it.
Now it's throwing this error - started yesterday (Dec21 2020) after the latest office update.
I have a workbook that is meant to build a new workbook using a starting template workbook with all of the code and creating a new version of an existing workbook (source workbook) (i.e, rollout new template features or add/remove pages as required.
The process goes through page by page to create the new pages in the template file, copying the corresponding page in the source workbook.
If works to a point but then throws this error.
To get from page to page I use ActiveSheet.Next.Activate in the source workbook (after trying a number of different means I found by the Googles, Including Phil's suggestion ) - it goes for a few and then I get the error still
The number of sheets in the source 32. At sheet 24, the next.activate - throws the error. Which is weird because there are 8 more pages before I would expect that it would complain about trying to activate a page that does not exist.
This is the code snipit that causes the problem
' Copy contents from old page to new page - if hidden, unhide it. If not present, then skip it
If OpMode <> "RCM" And FromSheetName <> "" Then
' Testing method
If WorksheetExists(FromSheetName) = True Then ' This determines that the sheet name does exist
Sheets("Spreadsheets").Activate 'Set to the first page before all of the control pages that follow
While ActiveSheet.Name <> FromSheetName ' keep activating the next page until the page name is the one I'm looking for
ActiveSheet.Next.Activate ************ this works for a random number of times but then throws the error before all of the sheets are activated 1 by 1.
' found the page
ActiveSheet.Visible = xlSheetVisible
' Get the "control" number from this page
FromNumber = CInt(Mid(Range("A1").Name.Name, 6, 3))
... do a bunch more stuff - Copying data cell by cell from the source workbook to the new workbook then next i to the next control and repeat the process.
Is there a way to fix this - it worked for years by simply activating the source sheet by name i.e., Sheets(Name).Select
Any insight would be much appreciated