Results 1 to 7 of 7

Fixing an error where "Thisworkbook" is duplicated

Threaded View

  1. #1
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,347

    Fixing an error where "Thisworkbook" is duplicated

    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
    Last edited by scottiex; 05-01-2017 at 06:51 PM.
    If you want something done right... find a forum and ask an online expert.

    Time flies like an arrow. Fruit flies like a banana.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 35
    Last Post: 01-13-2016, 02:16 AM
  2. = EMBED("Forms.ComboBox.1","") error "Reference not valid."
    By CatharinaCatharina in forum Excel General
    Replies: 2
    Last Post: 12-11-2014, 09:58 AM
  3. [SOLVED] If Not ActiveSheet.Range("A1").Value Like "apple" Then MsgBox "Error"
    By HerryMarkowitz in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-18-2014, 02:16 PM
  4. [SOLVED] How to Count number of "Error" and "OK" after the word "Instrument" found in table row
    By eltonlaw in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-17-2012, 06:26 AM
  5. Replies: 11
    Last Post: 05-15-2012, 01:22 PM
  6. Excel 2003 "Compile error in hidden module: ThisWorkbook"
    By JeffBrown in forum Excel General
    Replies: 1
    Last Post: 11-05-2007, 04:02 PM
  7. Replies: 5
    Last Post: 06-03-2005, 04:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1