Relatively new to this forum so I'll try to be brief. But it isn't a brief problem, as you will see in the list of things I have tried. All of this is being driven from Access using an Excel Application Object in VBA. I'm doing it that way because the required data sits in an Access database. Be warned that this is cross-posted on the Access World forums, where I am a member. But I would not cross-post this if I had an answer. (I'd implement the answer!)

I am building a family tree diagram using a SmartArt "Horizontal Hierarchy" Layout. But the tree goes so deep that I decided to generate diagrams limited to showing three generations per page. Then if I know more ancestors beyond that third generation (and trust me, I really DO know more), I make a new page starting from one of the grandparents from the first page and two more generations. The "longest" sequence is ten generations so takes five diagrams. The other tree branches stop after two or three such diagrams so I'm expecting less than twenty pages in total.

My sequence of operations is:

(a) Copy a template file (that contains one empty, hand-generated hierarchy layout) to a new file, and then rename the new file.
(b) Open the new file.
(c) As needed, copy the sheet named "Template" to a new sheet and start filling in the nodes of the hierarchy that appear on that sheet. "Starting" person, two parents, four grandparents. Seven people, three generations per sheet.
(d) Lather, rinse, repeat... and there is where things get slippery and go down the drain.

I am using recursive code to deal with the diagrams. I.e. when I fill in a "grandpa" slot, I check to see if more info exists, and if it does, I make that grandpa the starting person for whom to call the same code again - but a couple of generations deeper. This part actually works. It is on the return from recursion, i.e. when I run out of deeper generations so go back to prior pages, that things fall down.

Method #1 - fails when I find that I do NOT need another recursion, so I finish working on the new page (page 5 in this case) and return to the previous level. At that point when I try to update ANYTHING on the 4th page. I get the error as noted: #1004, Application-defined or Object-defined error. The recursion error handler issues a message and returns to itself on page 3. Ditto, another 1004 error, so it returns to page 2. Ditto, 1004 error, returns to page 1. At page 1, when it returns, the recursion is complete so the caller of the recursion closes the workbook file. When I open the file directly with Excel, the pages are there and the SmartArt nodes have been filled in but other information was NOT filled in. The aftermath of the 1004 error was that even really simple code operations stopped working. Something so simple as xWrkSht.Range("A13") = "text string" wouldn't work. In fact, based on single-stepping, it was that attempt to set a string into a cell on one of the previous pages that actually triggered the trap.

Method #2 -searching the web, I found articles saying that after copying whole worksheets several times, error 1004 is known to show up. The suggested fix was to close and re-open the workbook. So I tried that. The code closed the workbook every time immediately after it created a new page. Since I knew which page I had been working on, I was able to re-open it to the same worksheet. I did that, but saw no difference in the result. Same attempt to load a selected cell with text failed and triggered error 1004.

Method #3 - more web searches: It was suggested in a similar thread on Experts Exchange that rather than copying the page internally, that I copy it from the original read-only copy of the template file. I.e. an INTER-file copy rather than an INTRA-file copy. Tried that. No joy. (By the way, that solution was not something I had cross-posted. Someone else had a similar problem so I just decided to try the suggested solution.)

Method #4 - from the Access forum where this was cross-posted, someone suggested that I change my references to always start from the Excel Application Object, so I have really long cell or range references like xgXLApp.Workbooks(bknum).Worksheets(sheetname).Range... for everything. But no joy in that approach, either. Fails at the same place. After copying page 5, finishing it, and returning to page 4 to look for more opportunities to recurse, I still can't even store text in a cell.

Method #5 - I noticed that one bit of code DID run after recurson finished. The last phase of the tree generator was create one more page to build a table of contents, alphabetically sorted, showing on which page number a given person could be found. I created that page by a simple .Worksheets.Add operation - and it let me! Further, the table of contents was correctly populated, and even the multi-column sort worked correctly.

So I decided that instead of copying the worksheet, I would just ADD the worksheet and copy/paste the SmartArt object. But what happens when I do THAT is that the SmartArt gets copied to the new sheet as a PICTURE, not a SHAPE, and thus is not modifiable. I should add that both .Paste and .PasteSpecial have the same result, although I have not tried ALL of the options of .PasteSpecial yet.

Any suggestions? I'll cheerfully fix the code to copy worksheets as a whole if someone can suggest a good workaround. OR I'll copy the SmartArt - whichever way works.