This is going to be a) difficult to explain and b) even more difficult to solve (but not impossible ). Any takers? :D
EXPLANATION OF WHAT I WANT TO ACHIEVE:
INPUT: An active worksheet with cells containing values, formatting, borders etcetcera
OUTPUT: A VBA module containing code to re-create an (almost-) exact copy of this sheet
Another way to describe it - I am after an equivalent of de-compiling code. Except IDE would examine the sheet and work out what code is required to create an almost exact copy of the sheet.
THOUGHTS ON HOW TO ACHIEVE/POTENTIAL DIFFICULTIES:
I am aware that solving this will be extremely difficult. But I can't see it as being totally impossible.
At worst case scenario, IDE could be used to loop through each cell in the used range and examine the cell contents, cell border, cell formatting etc. (Of course that would be the least efficient way to do it. It would probably easier to examine the used range for contiguous "blocks" of cells containing the same similar formatting or borders etc. and then loop through these "blocks". e.g. IDE would loop through the used range and pick up all the cell borders, then it would loop through again and pick up all the cell values and so on).
I am also aware that the output code would be a total mess but, in most cases, it would probably much easier to tidy the output code rather than try to recreate the sheet from scratch with Macro Recorder.
If it makes it easier to solve this, I don't require an exact copy. Number formatting can be ignored. So could Conditional Formatting etc. In fact, Cell values, Cell formulas and Cell borders are the only essential items that I can think of.
OPTIONAL - WHY DO I NEED THIS:
I have a large number of XLSMs. I wish to convert a lot of these to XLAMs. Converting the "input sheets" to User Forms is tricky but not impossible. The big headache I am finding is how to dynamically recreate the "output sheets" in an active workbook. If this thread was solved, it would give me the nearest equivalent of macro recorder output on creating the said sheet - then it's just a matter of me manually tidying this code.
Bookmarks