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)

Please Login or Register  to view this content.

probably easier to find out what is wrong with the thing in the first place as you are just perpetuating the issue and prolonging your agony

have you tested either of the two solutions here
http://stackoverflow.com/questions/1...rkbook-objects

Well...

Copying to a new workbook (and fixing all the named ranges) works (as far as i can tell), which is what has me going down this track.
Of course I can do this manually (or instruct the users to do it manually when they have the issue - although copying named ranges will be a major pain) - but I like having things automated if possible.

Saving as xlsb didn't seem to properly fix the file. I imagine that might have "worked" if my files were a lot more simple but they have quite a lot going on.

I have no reason to suspect any particular macros that pass sheets to subs as parameters - its quite likely they don't run any that do that at all. Except in as far as they run SAP's Analysis for Office COMMs addin - and of course I won't have any access to that code and can't tell if that was the reason since we have not been able to repeat the error yet.

it works until it doesnt....
which is why you are needing to have a solution for when it doesnt
and one wouldnt go to this measure unless it happens often enough
meaning there is something fundamentally not working

anyway
code for Copying named ranges
https://excelribbon.tips.net/T008811...ed_Ranges.html

well, it is less that it is so common it is a problem for me. It only happened a 2 or 3 times (although who knows it might become more common).
But more about putting a solution on here (and in my toolbox) that is a bit more user friendly than the rest of the solutions one finds on the google search. (I.e. run this macro and follow instructions)

I was trying to copy over the ranges with a similar bit of code I googled but it seemed to keep referring to the original workbooks ranges (when I want to refer to those ranges in the new workbook.) Maybe i did something wrong, but regardless, if that is still an issue I think I can just edit the n.value to chop the file name off.

I take it there is no way to move the sheets without opening the file that I could try?

I take it there is no way to move the sheets without opening the file that I could try?

not that i am aware of

I was trying to copy over the ranges with a similar bit of code I googled but it seemed to keep referring to the original workbooks ranges (when I want to refer to those ranges in the new workbook.) Maybe i did something wrong, but regardless, if that is still an issue I think I can just edit the n.value to chop the file name off.

yes it does...sort of.
if you close the original workbook does the named range no longer work?
it should as long as the worksheets are named the same on the copied workbook

chopping n.value is plausible but then you would need some way of logically connecting the right worksheet to the right named range

OK this seems to get the job done.
I might think of a way to help with opening the file later. maybe some vba open the file in safe mode with as much turned off as possible...

Please Login or Register  to view this content.