@DevendraJain
Thank you for the offer. Though, because of the spreadsheet's proprietary nature I can't share it. And actually, the file has gone through many itinerations over the past few days, so I can't really give you a good clear example.
In the spreadsheet are named ranges, including reference in formulas, and in cell data validation rules. Those are a real challenge to get across.
Then in regards to external links, there are buttons from the form controls, and I found that the buttons cause cross-spreadsheet data links. So, they were not copied, but instead re-created.
I just wish Microsoft would publish an extensive list of how you can unintentionally create these links, and how to locate where they are. Excel is able to be notified of the external link, because it asks if you want to update the information in the link... but it refuses to tell you where that link is hiding. There are few ways to search, that will find SOME of them, but not all. And of course, it would be nice if there were a "save special" that would forbid the creation of external links.
I ended up taking several steps to avoid the link.
1st, I did a save-as from the spreadsheet that I was wanting to copy the data and formulas from. Then, I erased all the stuff I didn't want. That brought a lot of the things I wanted in. But, I still had an earlier spreadshee that I had started from scratch, and I was hoping to get that work into the save-as version.
2nd, from the spreadsheet that I had earlier created from scratch, I was able to copy the formulas, by setting Excel to show the formulas, and then copy/past those cells into Notepad. This would strip all the information about the origin of the data. I the copied that text, and pasted it back into Excel (in the one I just crated with the save-as). This successfully pasted the formulas without creating external links.
3rd. For the buttons that triggered macros, I manually copy/pasted the code for the macros in VBA. Then, manually recreated the buttons.
Finally, I also had to do a lot of re-work, because pasting those formulas, also lost all the formatting info, and the data validation was not possible to copy. But, after a few days of work, I was able to get my new spreadsheet working as I expected.
-Joe
