So there is an error that I have seen a few times where the "thisworkbook" object gets duplicated. (so if you go into the vba editor you will see two thisworkbook modules or maybe one thisworkbook and a thisworkbook1 module, maybe other modules will also be duplicated)
This will cause the sheet to probably crash. After some effort a retrieved copy of the file will probably be able to be opened.
I have found that the solution seems to be to just copy all the worksheets over to a new template workbook. So assuming I have a template with all the correct vba in it (as i will have) I want to write some fairly generic code to repair the file.
So this (embarrassingly badly written) code below might be of use to quite a few people given the messages I have seen online about this (you run it when the file is open and it will ask you to browse to a template then it will move the sheets to that template)
However I still have a couple of issues my problem is that
1) just moving the sheets seems to ruin my named ranges (is there anything else it might ruin? I want this as robust as possible)
2) in practice the file will crash when I open it meaning there is quite an annoying process to get it to open, which is the essential first step in the process. I'd like to be able to run this process without having to properly open the file if possible. (maybe there is a better methodology)
Sub Rebuildsheet()
Dim wb1 As String, wb2n As String, wb2 As Workbook
Dim mySaver As FileDialog
Dim sh As Worksheet
With ActiveWorkbook
.Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "MyTemp"
End With
Application.ScreenUpdating = False
wb1 = ActiveWorkbook.Name
Application.DisplayAlerts = False
wb2n = Application.GetOpenFilename("Select Template Excel Files. (*.xls*),*.xls*")
Workbooks.Open Filename:=wb2n
Set wb2 = ActiveWorkbook
On Error Resume Next
For Each sh In Workbooks(wb1).Worksheets
If sh.Name <> "MyTemp" Then
wb2.Activate
wb2.Sheets(sh.Name).Delete
Workbooks(wb1).Sheets(sh.Name).Move After:=wb2.Sheets(wb2.Sheets.Count)
Else: End If
Next
Workbooks(wb1).Close
MsgBox "Finished - please save file in appropriate location"
' ActiveWorkbook.SaveAs Filename:=Workbooks(wb1).path & "\repaired.xlsm"
Set mySaver = Application.FileDialog(msoFileDialogSaveAs)
With mySaver
.Title = "Save this repair as..."
.InitialFileName = "repaired.xlsm"
.Show
ActiveWorkbook.SaveAs Filename:=.SelectedItems(1), _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Bookmarks